Reporting Aggregated Data Using the Group Functions

1. Reporting Aggregated Data Using the Group Functions

2. 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 using the GROUP BY clause • Include or exclude grouped rows by using the HAVING clause

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

4. Types of Group Functions

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

6. Distinct and Null values • Guidelines for Using Group Functions • DISTINCT makes the function consider only non duplicate values; ALL makes it consider every value, including duplicates. The default is ALL and therefore does not need to be specified. • All group functions ignore null values. To substitute a value for null values, use the NVL, NVL2, or COALESCE functions. SELECT SUM (NVL(Sales,100)) FROM Sales_Data; hence the sum of the 3 rows is 300 + 100 + 150 = 550

7. 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 =‘IT_PROG'; 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) ,MIN(First_name) ,MAX(First_name) FROM employees;

8. Using AVG and SUM Functions (another example) You can use AVG and SUM for numeric data. SQL> SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE job LIKE 'SALES%'; AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) -------- --------- --------- --------- 1400 1600 1250 5600

9. Using MIN and MAX Functions (another example) You can use MIN and MAX for any datatype. SQL> SELECT MIN(hiredate), MAX(hiredate) 2 FROM emp; MIN(HIRED MAX(HIRED --------- --------- 17-DEC-80 12-JAN-83

10. Example select max(hire_date) from employees • Display the last one get a job in employees • Display the name of the employee who get the max salary ERROR cann’t display any column with a group function • Display the total of salary for all employees who work in department number 60? select last_name,max(salary) from employees select sum(salary) from employees where department_id =60

11. Using the COUNT Function COUNT(*) returns the number of rows in a table. SQL> SELECT COUNT(*) 2 FROM emp 3 WHERE deptno = 30; COUNT(*) --------- 6

12. 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

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

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

15. 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];

16. 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

17. 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

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

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

20. 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 HAVING ERRORgroup function is not allowed here Note:Cannot use the WHERE clause to restrict groups

21. 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  Column missing in the GROUPBY clause SELECT department_id, COUNT(last_name) FROM employees GROUP BY department_id; 

22. Restricting Group Results(Excluding Group Results) EMPLOYEES The maximumsalaryper department when it is greater than$10,000 …

23. 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]; • Having : deal with results of Group function • Where: deal with real value in table

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

25. 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);

26. TRY 1. SELECT department_id, MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary)>10000 ORDER BY department_id 2. SELECT department_id, MAX(salary) FROM employees HAVING MAX(salary)>10000 GROUP BY department_id ORDER BY MAX(salary)

27. Summary SELECT column, group_function (column) FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; Order of evaluation of the clauses: • WHERE clause • GROUP BY clause • HAVING clause

28. Using Subqueries to Solve Queries

29. Objectives After completing this lesson, you should be able to do the following: • Define subqueries • Describe the types of problems that subqueries can solve • List the types of subqueries • Write single-row and multiple-row subqueries

30. Using a Subqueryto Solve a Problem Who has a salary greater than Abel’s? Main query: Which employees have salaries greater than Abel’s (last name) salary? Subquery: What is Abel’s salary?

31. Subquery Syntax • The subquery (inner query) executes once before the main query (outer query). • The result of the subquery is used by the main query. SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

32. Using a Subquery SELECT first_name, salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel'); 11000

33. Guidelines for Using Subqueries • Enclose subqueries in parentheses. • Place subqueries on the right side of the comparison operator. • Do not add an ORDER BY clause to a subquery. • Use single-row operators with single-row subqueries. • Use multiple-row operators with multiple-row subqueries.

34. Types of Subqueries • Single-row subquery • Multiple-row subquery Main query returns Subquery ST_CLERK Main query returns ST_CLERK SA_MAN Subquery

35. Single-Row Subqueries • Return only one row • Use single-row comparison operators

36. Display the employees whose job title is the same as that of employee 141 Executing Single-Row Subqueries and salary greater than the salary of employee 143. SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141) AND salary > (SELECT salary FROM employees WHERE employee_id = 143); ST_CLERK 2600

37. Using Group Functions in a Subquery Display the last name and salary of employees that earn the minimum salary. SELECT last_name, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees); 2100

38. The HAVING Clause with Subqueries Display all the departments that have a minimum salary greater than that of department 20. SELECT department_id, MIN(salary) FROM employees GROUP BY department_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 20); 6000

39. find the job with the lowest average salary. Select job_id, avg(salary) from employees Group by job_id Having avg(salary)=(select min(avg(salary)) lowest_Avg_Sal from employees group by job_id)

40. What Is Wrong with This Statement? SELECT employee_id, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id); Single-row operator with multiple-row subquery ERROR at line 4: ORA-01427: single-row subquery returns more thanone row

41. Will This Statement Return Rows? SELECT last_name, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = ‘Anas'); no rows selected Subquery returns no values.

42. Multiple-Row Subqueries • Return more than one row • Use multiple-row comparison operators

43. Using IN Operatorin Multiple-Row Subqueries SQL> SELECT ename, sal, deptno 2 FROM emp • WHERE sal IN ( SELECT MIN(sal) • FROM emp • GROUP BY deptno); SQL> SELECT ename, sal, deptno 2 FROM emp 3 WHERE sal IN (800, 950, 1300);

44. Using the ANY Operator in Multiple-Row Subqueries Display employees whose salary is less than any IT programmer and who aren’t IT programmer not IT programmer and less than the maximum salary(9000) SELECT first_name, job_id, salary FROM employees WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; 9000, 6000, 4800, 4200 …

45. Using the ALL Operatorin Multiple-Row Subqueries Display employees whose salary is less than all IT programmers and who aren’t IT programmer SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG'; 9000, 6000, 4800,4200

46. Display the employees whose salary is greater than the average salaries of all the departments. SELECT first_name, salary FROM employees WHERE salary > ALL (SELECT avg(salary) FROM employees group by department_id)

47. Display the first_name of all employees whose mange other employees SELECT first_name FROM employees WHERE employee_id IN (SELECT manager_id FROM employees)

48. Null Values in a Subquery SELECT emp.last_name FROM employees emp WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr); no rows selected

49. Summary In this lesson, you should have learned how to: • Identify when a subquery can help solve a question • Write subqueries when a query is based on unknown values SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);