Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Unlike single-row functions, group functions operate on sets of rows to give one result per group. These sets may be the whole table or the.
Typology: Exams
1 / 43
This page cannot be seen from the preview
Don't miss anything!
What Are Group Functions?
Group functions operate on sets of rows to give one result per group.
Types of Group Functions
- VARIANCE
Using AVG and SUM Functions
Group Functions You can use AVG, SUM. MIN, and MAX functions against columns that can store numeric data. The example on the slide displays the average, highest, lowest, and sum of monthly salaries for all salespeople.
Using MIN and MAX Functions
Using MIN , MAX for numeric data types:
Using MIN and MAX Functions
Group Functions (continued) The following example displays the employee name that is first and the employee name that is the last in an alphabetized list of all employees.
Using the COUNT Function
The COUNT Function The COUNT function has two formats:
COUNT(*) returns the number of rows in a table, including duplicate rows and rows containing null values in any of the columns.
If a WHERE clause is included in the SELECT statement COUNT(*) returns the number of rows that satisfies the condition in the WHERE clause.
In contrast. COUNT( expr ) returns the number of nonnull rows in the column identified by expr.
Using the COUNT Function
The COUNT Function (continued) COUNT( expr ) returns the number of nonnull rows in the column identified by expr. The slide example displays the number of employees in departme'nt 30 who can earn a commission. Notice that the result gives the total number of rows to be four because two employees in department 30 cannot earn a commission and contain a null value in the COMM column.
SELECT COUNT(comm) FROM emp WHERE deptno = 20 ;
FROM emp WHERE comm IS NULL ;
Group Functions and Null Values
SELECT AVG(comm)
FROM emp;
AVG(comm) 550
Group Functions and Null Values All group functions except COUNT (*) ignore null values in the column. In the slide example, the average is calculated based only on the rows in the table where a valid value is stored in the COMM column. The average is calculated as total commission being paid to all employees divided by the number of employees receiving commission (4).
Using the NVL Function with Group Functions
SELECT AVG(NVL (comm,0))
FROM emp;
AVG(NVL(comm , 0)) 157,
Group Functions and Null Values (continued) The NVL function forces group functions to include null values. In the slide example, the average is calculated based on all rows in the table regardless of whether null values are stored in the COMM column. The average is calculated as total commission being paid to all employees divided by the total number of employees in the company (14).
Creating Groups of Data: GROUP BY Clause
SELECT column, group_function (column) FROM table [WHERE condition] [GROUP BY group by] [ORDER BY column];
The GROUP BY Clause
You can use the GROUP BY clause to divide the rows in a table into groups.
You can then use the group functions to return summon' information for each group.
In the syntax:
group by expression specifies columns whose values determine the basis for grouping rows
Guidelines
If you include a group function in a SELECT clause, you cannot select individual results as well unless the individual column appears in the GROUP BY clause. You will receive an error-message if you fail to include the column list.
Using a WHERE clause, you can preexclude rows before dividing them into groups.
You must include the columns in the GROUP BY clause.
You cannot use the column alias in the GROUP BY clause.
Using the GROUP BY Clause
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ;
The GROUP BY Clause (continued)
When using the GROUP BY clause, make sure that all columns in the SELECT list that are not in the group functions are included in the GROUP BY clause. The example on the slide displays the department number and the average salary for each department. Here is how this SELECT statement, containing a GROUP BY clause, is evaluated:
The SELECT clause specifies the columns to be retrieved:
Department number column in the EMP table
The average of all the salanes in the group you specified in the GROUP BY clause
The FROM clause specifies the tables that the database must access: the EMP table.
The WHERE clause specifies the rows to be retrieved. Since there is no WHERE clause, bv default all rows are retrieved.
Creating Groups of Data
SELECT deptno , AVG(sal)
FROM emp
GROUP BY deptno; DEPTNO AVG(SAL) 30 1 566, 20 2175 10 2916,
Using the GROUP BY Clause
SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno;
