bordoloi and bock chapter 5 : aggregate row functions

Bordoloi and BockBordoloi and Bock

Chapter 5 : Aggregate Row Functions.Chapter 5 : Aggregate Row Functions.


AGGREGATE ROW FUCTIONSAGGREGATE ROW FUCTIONS

• Aggregate Row functions give the user the Aggregate Row functions give the user the ability to answer business questions such as:ability to answer business questions such as:

What is the average salary of an employee What is the average salary of an employee in the company?in the company?

What were the total salaries for a particular What were the total salaries for a particular year?year?

What are the maximum and minimum What are the maximum and minimum salaries in the Computer’s Department? salaries in the Computer’s Department?



• Aggregate functions perform a variety of Aggregate functions perform a variety of actions such as counting all the rows in a actions such as counting all the rows in a table, averaging a column's data, and table, averaging a column's data, and summing numeric data.summing numeric data.

• Aggregates can also search a table to find Aggregates can also search a table to find the highest "MAX" or lowest "MIN" values the highest "MAX" or lowest "MIN" values in a column. in a column.



• List of aggregate functions including their syntax List of aggregate functions including their syntax and use.and use.

Function Syntax Function Use

SUM( [ALL | DISTINCT] expression )

The total of the (distinct) values in a numeric column/expression.

AVG( [ALL | DISTINCT] expression )

The average of the (distinct) values in a numeric column/expression.

COUNT( [ALL | DISTINCT] expression )

The number of (distinct) non-NULL values in a column/expression.

COUNT(*) The number of selected rows.

MAX(expression) The highest value in a column/expression.

MIN(expression) The lowest value in a column/expression.



• There are two rules that you must understand and There are two rules that you must understand and follow when using aggregates: follow when using aggregates:

• Aggregate functions can be used in both the SELECT Aggregate functions can be used in both the SELECT and HAVING clauses (the HAVING clause is covered and HAVING clauses (the HAVING clause is covered later in this chapter).later in this chapter).

• Aggregate functions cannotAggregate functions cannot be used in a WHERE be used in a WHERE clause.clause.


EXAMPLEEXAMPLE

• The following query The following query is wrong and will produce is wrong and will produce the Oracle ORA-00934 the Oracle ORA-00934 group function is not group function is not allowed hereallowed here error message. error message.

SELECT *SELECT *

FROM employeeFROM employee

WHERE emp_salary > AVG(emp_salary);WHERE emp_salary > AVG(emp_salary);

ERROR at line 3: ORA-00934: group function is not ERROR at line 3: ORA-00934: group function is not allowed here.allowed here.


COUNT( )COUNT( )• If a manager needs know how many employees work in If a manager needs know how many employees work in

the organization, COUNT(*) can be used to produce this the organization, COUNT(*) can be used to produce this information. information.

• The COUNT(*) function counts all rows in a table. The COUNT(*) function counts all rows in a table. • The wild card asterisk (*) would be used as the The wild card asterisk (*) would be used as the

parameter in the function. parameter in the function.

SELECT COUNT(*)SELECT COUNT(*)FROM employee;FROM employee;

COUNT(*)COUNT(*) ---------------- 88


COUNT( )COUNT( )

• The result table for the COUNT(*) function The result table for the COUNT(*) function is a single is a single scalarscalar value. value.

• Notice that the result table has a column Notice that the result table has a column heading that corresponds to the name of the heading that corresponds to the name of the aggregate function specified in the SELECT aggregate function specified in the SELECT clause. clause.

• The output column can be assigned a more The output column can be assigned a more meaningful column name as is shown in the meaningful column name as is shown in the revised query .revised query .


COUNT( )COUNT( )

• This is accomplished by simply listing the desired This is accomplished by simply listing the desired column name inside double-quotes after the column name inside double-quotes after the aggregate function specification.aggregate function specification.

SELECT COUNT(*) "Number of SELECT COUNT(*) "Number of Employees"Employees"

FROM employee;FROM employee;

Number of EmployeesNumber of Employees

------------------------------------------------------

88


COUNT( )COUNT( )

• COUNT(*) is used to count all the rows in a table.COUNT(*) is used to count all the rows in a table.• COUNT(column name) does almost the same thing. COUNT(column name) does almost the same thing.

The difference is that you may define a specific The difference is that you may define a specific column to be counted.column to be counted.

• When column name is specified in the COUNT When column name is specified in the COUNT function, rows containing a NULL value in the function, rows containing a NULL value in the specified column are omitted. specified column are omitted.

• A NULL value stands for “unknown” or A NULL value stands for “unknown” or “unknowable” and must not be confused with a blank “unknowable” and must not be confused with a blank or zero. or zero.


COUNT ( )COUNT ( )SELECT COUNT(emp_superssn) "Number Supervised SELECT COUNT(emp_superssn) "Number Supervised Employees"Employees"FROM employee;FROM employee;

Number Supervised EmployeesNumber Supervised Employees------------------------------------------------------

77

• In contrast the count(*) will count each row In contrast the count(*) will count each row regardless of NULL values.regardless of NULL values.

SELECT COUNT(*) "Number of Employees“SELECT COUNT(*) "Number of Employees“FROM employee;FROM employee;Number of EmployeesNumber of Employees--------------------------------------

88


Using the AVG FunctionUsing the AVG Function

• AVG function is used to compute the average value AVG function is used to compute the average value for the for the emp_salaryemp_salary column in the column in the employeeemployee table. table.

• For example, the following query returns the For example, the following query returns the average of the employee salaries. average of the employee salaries.

SELECT AVG(emp_salary) "Average Employee SELECT AVG(emp_salary) "Average Employee Salary"Salary"

FROM employee;FROM employee; Average Employee SalaryAverage Employee Salary---------------------------------------------- $35,500$35,500


More ExamplesMore Examples

• What is the average salary What is the average salary offeredoffered to employees? to employees?

• This question asks you to incorporate the concept of This question asks you to incorporate the concept of computing the average of the distinct salaries paid by the computing the average of the distinct salaries paid by the organization.organization.

• The same query with the DISTINCT keyword in the The same query with the DISTINCT keyword in the aggregate function returns a different average.aggregate function returns a different average.

SELECT AVG(DISTINCT emp_salary) "Average Employee SELECT AVG(DISTINCT emp_salary) "Average Employee Salary"Salary"


Average Employee SalaryAverage Employee Salary

----------------------------------------------

$38,200$38,200


Using the SUM FunctionUsing the SUM Function

• The SUM function can compute the total of a The SUM function can compute the total of a specified table column.specified table column.

• The SELECT statement shown here will return the The SELECT statement shown here will return the total of the total of the emp_salaryemp_salary column from the column from the employeeemployee table. table.

SELECT SUM(emp_salary) "Total Salary"SELECT SUM(emp_salary) "Total Salary"


Total SalaryTotal Salary

------------------------

$284,000$284,000



• If management is preparing a budget for various If management is preparing a budget for various departments, you may be asked to write a query to departments, you may be asked to write a query to compute the total salary for different departments. compute the total salary for different departments.

• The query shown here will compute the total The query shown here will compute the total emp_salary for employees assigned to department emp_salary for employees assigned to department #7.#7.

SELECT SUM(emp_salary) "Total Salary Dept 7"SELECT SUM(emp_salary) "Total Salary Dept 7"FROM employeeFROM employeeWHERE emp_dpt_number = 7;WHERE emp_dpt_number = 7;

Total Salary Dept 7Total Salary Dept 7--------------------------------------

$136,000$136,000


MIN and MAX FunctionsMIN and MAX Functions

• The MIN function returns the lowest value stored in The MIN function returns the lowest value stored in a data column.a data column.

• The MAX function returns the largest value stored The MAX function returns the largest value stored in a data column. in a data column.

• Unlike SUM and AVG, the MIN and MAX Unlike SUM and AVG, the MIN and MAX functions work with both numeric and character data functions work with both numeric and character data columns. columns.


ExampleExample

• A query that uses the MIN function to find the lowest value A query that uses the MIN function to find the lowest value stored in the stored in the emp_last_nameemp_last_name column of the column of the employeeemployee table. table.

• This is analogous to determine which employee's last name This is analogous to determine which employee's last name comes first in the alphabet. comes first in the alphabet.

• Conversely, MAX() will return the employee row where last Conversely, MAX() will return the employee row where last name comes last (highest) in the alphabet. name comes last (highest) in the alphabet.

SELECT MIN(emp_last_name), MAX(emp_last_name)SELECT MIN(emp_last_name), MAX(emp_last_name)


MIN(EMP_LAST_NAME) MAX(EMP_LAST_NAME)MIN(EMP_LAST_NAME) MAX(EMP_LAST_NAME)

------------------------- ------------------------------------------------ -----------------------

Amin ZhuAmin Zhu


Using GROUP BY with Aggregate Using GROUP BY with Aggregate FunctionsFunctions

• The power of aggregate functions is greater when The power of aggregate functions is greater when combined with the GROUP BY clause. combined with the GROUP BY clause.

• In fact, the GROUP BY clause is rarely used without In fact, the GROUP BY clause is rarely used without an aggregate function. an aggregate function.

• It is possible to use the GROUP BY clause without It is possible to use the GROUP BY clause without aggregates, but such a construction has very limited aggregates, but such a construction has very limited functionality, and could lead to a result table that is functionality, and could lead to a result table that is confusing or misleading. confusing or misleading.


ExampleExample

• The following query displays how many employees The following query displays how many employees work for each department?work for each department?

SELECT emp_dpt_number "Department",SELECT emp_dpt_number "Department",COUNT(*) "Department Count"COUNT(*) "Department Count"FROM employeeFROM employeeGROUP BY emp_dpt_number;GROUP BY emp_dpt_number;

Department Department CountDepartment Department Count---------- -------------------------- ----------------

1 11 1 3 33 3 7 47 4


GROUP BY ClauseGROUP BY Clause

• Some RDBMs provides considerable Some RDBMs provides considerable flexibility in specifying the GROUP BY flexibility in specifying the GROUP BY clause.clause.

• The column name used in a GROUP BY does The column name used in a GROUP BY does not have to be listed in the SELECT clause; not have to be listed in the SELECT clause; however, it must be a column name from one however, it must be a column name from one of the tables listed in the FROM clause. of the tables listed in the FROM clause.


ExampleExample

• We could rewrite the last query without specifying the We could rewrite the last query without specifying the emp_dpt_numberemp_dpt_number column as part of the result table, column as part of the result table, but as you can see below, the results are rather cryptic but as you can see below, the results are rather cryptic without the without the emp_dpt_numberemp_dpt_number column to identify the column to identify the meaning of the aggregate count. meaning of the aggregate count.

SELECT COUNT(*) "Department Count"SELECT COUNT(*) "Department Count"FROM employeeFROM employeeGROUP BY emp_dpt_number;GROUP BY emp_dpt_number;

Department CountDepartment Count--------------------------------

11 33 44


ExampleExample

However, the reverse is NOT true!However, the reverse is NOT true!

SELECT emp_dpt_number, COUNT(*) "Department SELECT emp_dpt_number, COUNT(*) "Department Count"Count"


SELECT emp_dpt_number, COUNT(*) "Department SELECT emp_dpt_number, COUNT(*) "Department Count"Count"

**

ERROR at line 1:ERROR at line 1:

ORA-00937: not a single-group group functionORA-00937: not a single-group group function


GROUP BY ClauseGROUP BY Clause

• To keep it simple, just remember the following:To keep it simple, just remember the following:

1. If you have column name(s) AND Aggr. 1. If you have column name(s) AND Aggr. Function(s) in the SELECT clause, then you Function(s) in the SELECT clause, then you MUST also have a GROUP BY clause.MUST also have a GROUP BY clause.

2. The column name(s) in the SELECT 2. The column name(s) in the SELECT clause MUST match column name(s) listed in clause MUST match column name(s) listed in the GROUP BY clause. the GROUP BY clause.


ExampleExample

SELECT emp_dpt_number "Department",SELECT emp_dpt_number "Department",

emp_gender "Gender",emp_gender "Gender",

COUNT(*) "Department Count"COUNT(*) "Department Count"


GROUP BY emp_dpt_number;GROUP BY emp_dpt_number;


ORA-00979: not a GROUP BY expressionORA-00979: not a GROUP BY expression


ExampleExample


emp_gender "G",emp_gender "G",

COUNT(*) “Employee Count"COUNT(*) “Employee Count"


GROUP BY emp_dpt_number, emp_gender;GROUP BY emp_dpt_number, emp_gender;

Department G Employee CountDepartment G Employee Count

---------- - ------------------------ - --------------

1 M 11 M 1

3 F 23 F 2

3 M 13 M 1

7 F 17 F 1

7 M 37 M 3


Using GROUP BY With a WHERE ClauseUsing GROUP BY With a WHERE Clause• The WHERE clause works to eliminates data table rows The WHERE clause works to eliminates data table rows

from consideration before any grouping takes place. from consideration before any grouping takes place. • The query shown here produces an average hours worked The query shown here produces an average hours worked

result table for employees with a social security number result table for employees with a social security number that is larger than 999-66-0000. that is larger than 999-66-0000.

SELECT work_emp_ssn SSN, SELECT work_emp_ssn SSN, AVG(work_hours) "Average Hours Worked"AVG(work_hours) "Average Hours Worked"FROM assignment FROM assignment WHERE work_emp_ssn > 999660000WHERE work_emp_ssn > 999660000GROUP BY work_emp_ssn;GROUP BY work_emp_ssn;

SSN Average Hours WorkedSSN Average Hours Worked--------- ----------------------------- --------------------999666666999666666999887777 20.5999887777 20.5999888888 21.5999888888 21.5


Using GROUP BY With an ORDER BY ClauseUsing GROUP BY With an ORDER BY Clause

• The ORDER BY clause allows you to specify The ORDER BY clause allows you to specify how rows in a result table are sorted. how rows in a result table are sorted.

• The default ordering is from smallest to largest The default ordering is from smallest to largest value. value.

• A GROUP BY clause in a SELECT statement A GROUP BY clause in a SELECT statement will determine the sort order of rows in a result will determine the sort order of rows in a result table.table.

• The sort order can be changed by specifying an The sort order can be changed by specifying an ORDER BY clause after the GROUP BY ORDER BY clause after the GROUP BY clause. clause.


Using GROUP BY With an ORDER BY ClauseUsing GROUP BY With an ORDER BY Clause

SELECT emp_dpt_number "Department", SELECT emp_dpt_number "Department", AVG(emp_salary) "Average Salary"AVG(emp_salary) "Average Salary"


GROUP BY emp_dpt_numberGROUP BY emp_dpt_number

ORDER BY AVG(emp_salary);ORDER BY AVG(emp_salary);

Department Average SalaryDepartment Average Salary

---------- ------------------------ --------------

3 $31,0003 $31,000

7 $34,0007 $34,000

1 $55,0001 $55,000


GROUP BY With a HAVING ClauseGROUP BY With a HAVING Clause

• The HAVING clause is used for aggregate The HAVING clause is used for aggregate functions in the same way that a WHERE functions in the same way that a WHERE clause is used for column names and clause is used for column names and expressions. expressions.

• The HAVING and WHERE clauses do the The HAVING and WHERE clauses do the same thing, that is filter rows from inclusion in same thing, that is filter rows from inclusion in a result table based on a condition.a result table based on a condition.

• a WHERE clause is used to filter rows a WHERE clause is used to filter rows BEFOREBEFORE the GROUPING action. the GROUPING action.

• a HAVING clause filters rows a HAVING clause filters rows AFTER AFTER the the GROUPING action. GROUPING action.






HAVING AVG(emp_salary) > 33000;HAVING AVG(emp_salary) > 33000;


---------- ------------------------ --------------

1 $55,0001 $55,000

7 $34,0007 $34,000


Combining HAVING Clause with Where clause Combining HAVING Clause with Where clause



WHERE emp_dpt_number <> 1WHERE emp_dpt_number <> 1




---------- ------------------------ --------------

7 $34,0007 $34,000



Conceptually, SQL performs the following steps in the Conceptually, SQL performs the following steps in the query given above.query given above.

1. The WHERE clause filters rows that do not meet the 1. The WHERE clause filters rows that do not meet the conditionconditionemp_dpt_number <>emp_dpt_number <> 1. 1.2. The GROUP BY clause collects the surviving rows 2. The GROUP BY clause collects the surviving rows into one or more groups for each unique into one or more groups for each unique emp_dpt_numberemp_dpt_number..3. The aggregate function calculates the average salary 3. The aggregate function calculates the average salary for each for each emp_dpt_numberemp_dpt_number grouping. grouping.4. The HAVING clause filters out the rows from the 4. The HAVING clause filters out the rows from the result table that do not meet the condition average salary result table that do not meet the condition average salary greater than $33,000.greater than $33,000.




COUNT(*) "Department Count",COUNT(*) "Department Count",

MAX(emp_salary) "Top Salary",MAX(emp_salary) "Top Salary",

MIN(emp_salary) "Low Salary"MIN(emp_salary) "Low Salary"



HAVING COUNT(*) >= 3;HAVING COUNT(*) >= 3;

Department Department Count Top Salary Low SalaryDepartment Department Count Top Salary Low Salary

---------- ---------------- ---------- -------------------- ---------------- ---------- ----------

3 3 $43,000 $25,0003 3 $43,000 $25,000

7 4 $43,000 $25,0007 4 $43,000 $25,000



• The HAVING clause is a conditional option The HAVING clause is a conditional option that is directly related to the GROUP BY that is directly related to the GROUP BY clause option because a HAVING clause clause option because a HAVING clause eliminates rows from a result table based on eliminates rows from a result table based on the result of a GROUP BY clause. the result of a GROUP BY clause.

• In Oracle, A HAVING clause will In Oracle, A HAVING clause will notnot work work without a GROUP BY clause.without a GROUP BY clause.



SELECT emp_dpt_number, SELECT emp_dpt_number, AVG(emp_salary)AVG(emp_salary)




ORA-00937: not a single-group ORA-00937: not a single-group group functiongroup function

bordoloi and bock chapter 5 : aggregate row functions

Documents