Reporting Aggregated Data Using the Group Functions

Reporting Aggregated DataUsing the Group Functions

Objectives • After completing this lesson, you should be able to do the following: • Identify the available group functions • Describe the use of group functions • Group data by using the GROUP BY clause • Include or exclude grouped rows by using the HAVING clause

What Are Group Functions? • Group functions operate on sets of rows to give one result per group. EMPLOYEES Maximum salary in EMPLOYEEStable …

Group functions Types of Group Functions • AVG • COUNT • MAX • MIN • STDDEV • SUM • VARIANCE

Group Functions: Syntax SELECT [column,] group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [ORDER BY column];

Using the AVG and SUM Functions • You can use AVG and SUM for numeric data. SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees WHERE job_id LIKE '%REP%';

Using the MIN and MAX Functions • You can use MIN and MAX for numeric, character, and date data types. SELECT MIN(hire_date), MAX(hire_date) FROM employees;

Using the COUNT Function • COUNT(*) returns the number of rows in a table: • COUNT(expr) returns the number of rows with non-null values for the expr: SELECT COUNT(*) FROM employees WHERE department_id = 50; 1 SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80; 2

Using the DISTINCT Keyword • COUNT(DISTINCT expr) returns the number of distinct non-null values of the expr. • To display the number of distinct department values in the EMPLOYEES table: SELECT COUNT(DISTINCT department_id) FROM employees;

Group Functions and Null Values • Group functions ignore null values in the column: • The NVL function forces group functions to include null values: SELECT AVG(commission_pct) FROM employees; 1 SELECT AVG(NVL(commission_pct, 0)) FROM employees; 2

4400 9500 3500 6400 10033 Creating Groups of Data EMPLOYEES Averagesalary in EMPLOYEEStable for each department …

Creating Groups of Data: GROUPBY Clause Syntax • You can divide rows in a table into smaller groups by using the GROUPBY clause. SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

Using the GROUPBY Clause • All columns in the SELECT list that are not in group functions must be in the GROUPBY clause. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;

Using the GROUPBY Clause • The GROUPBY column does not have to be in the SELECT list. SELECT AVG(salary) FROM employees GROUP BY department_id ;

Grouping by More Than One Column EMPLOYEES Add the salaries in the EMPLOYEES table foreach job,grouped bydepartment …

Using the GROUPBY Clause on Multiple Columns SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;

Illegal Queries Using Group Functions • Any column or expression in the SELECT list that is not an aggregate function must be in the GROUPBY clause: SELECT department_id, COUNT(last_name) FROM employees; SELECT department_id, COUNT(last_name) * ERROR at line 1: ORA-00937: not a single-group group function Column missing in the GROUPBY clause

Illegal Queries Using Group Functions • You cannot use the WHERE clause to restrict groups. • You use the HAVING clause to restrict groups. • You cannot use group functions in the WHERE clause. SELECT department_id, AVG(salary) FROM employees WHERE AVG(salary) > 8000 GROUP BY department_id; WHERE AVG(salary) > 8000 * ERROR at line 3: ORA-00934: group function is not allowed here Cannot use the WHERE clause to restrict groups

Restricting Group Results EMPLOYEES The maximumsalaryper department when it is greater than$10,000 …

Restricting Group Results with the HAVING Clause • When you use the HAVING clause, the Oracle server restricts groups as follows: 1. Rows are grouped. 2. The group function is applied. 3. Groups matching the HAVING clause are displayed. SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];

Using the HAVING Clause SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ;

Using the HAVING Clause SELECT job_id, SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM(salary) > 13000 ORDER BY SUM(salary);

Nesting Group Functions • Display the maximum average salary: SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;

Summary • In this lesson, you should have learned how to: • Use the group functionsCOUNT, MAX, MIN, and AVG • Write queries that use the GROUPBY clause • Write queries that use the HAVING clause SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];

Practice 4: Overview • This practice covers the following topics: • Writing queries that use the group functions • Grouping by rows to achieve more than one result • Restricting groups by using the HAVING clause

Reporting Aggregated Data Using the Group Functions