1 / 14

Group Functions Using GROUP BY clause

Group Functions Using GROUP BY clause. Week 5 – Chapter 5. Objectives. Group data using the GROUP BY clause Include or exclude grouped rows results by using the HAVING clause. Group Fns Without GROUP BY.

nora-white
Download Presentation

Group Functions Using GROUP BY clause

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Group Functions Using GROUP BY clause Week 5 – Chapter 5

  2. Objectives • Group data using the GROUP BY clause • Include or exclude grouped rows results by using the HAVING clause

  3. Group Fns Without GROUP BY Group functions without a GROUP BY clause operate on one set of rows to give one row result SELECT MAX(salary) MAXSAL FROM employees; EMPLOYEES ID SALARY 100 24000 101 17000 102 17000 103 9000 … 178 7000 200 4400 201 13000 202 6000 205 12000 206 8300 maximum salary in the EMPLOYEES table MAXSAL 24000

  4. GROUP BY Clause Divide rows into smaller subsets or groups by using the GROUP BY clause. SELECT [column], group_function(column), ... FROM table [WHERE condition] [GROUP BY column] [HAVING expression] [ORDER BY column];

  5. Group Fns With GROUP BY Group functions with a GROUP BY clause operate on sets of rows to give 1 row result per set of rows SELECT department_id DEPT, MAX(salary) MAX FROM employees GROUP BY department_id; EMPLOYEES DEPTSALARY 10 4400 2013000 20 6000 505800 50 3500 50 3100 50 2500 50 2600 609000 606000 604200 … 7000 maximum salary in each dept in EMPLOYEES table DEPT MAX 10 4400 20 13000 … 7000

  6. Notes on GROUP BY Clause • A SELECT statement with a group function(s) and no GROUP BY clause returns only one row as its result • A SELECT statement with a GROUP BY clause can return multiple rows as its result • If you include a group function in a SELECT clause, you cannot display values from individual rows unless the individual column is in the GROUP BY clause • The GROUP BY column does not have to be in the SELECT list; eg: SELECT SUM(salary) FROM employees GROUP BY department_id

  7. Group Data on More Than 1 Column Data can be grouped into subsets by more than column. For example, salary could be totaled by job_id within each department as follows: SELECT department_id,job_id,SUM(salary) FROM employees GROUP BY department_id, job_id NOTE: All columns not used with group functions that are in the SELECT clause must be listed in the GROUP BY clause DEPARTMENT_ID JOB_IDSUM(SALARY) SA_REP7000 10AD_ASST 4400 20MK_MAN13000 20MK_REP6000 50ST_MAN5800 … 110AC_ACCOUNT8300

  8. Group Data on more than 1 Column If the order of the columns is changed in the GROUP BY clause then the data is simply presented in a different order. For example, salary could be totaled by department_id within each job as follows: SELECT job_id, department_id,job_id,SUM(salary) FROM employees GROUP BY job_id, department_id JOB_IDDEPARTMENT_IDSUM(SALARY) AD_VP9034000 AC_MGR 11012000 MK_MAN2013000 MK_REP206000 SA_MAN8010500 SA_REP7000 SA_REP8019600 … AC_ACCOUNT1108300

  9. Group Function Error • You will receive an error message if you include a group function and GROUP BY clause and an expression referencing a non-group column in a SELECT clause. Example: SELECT last_name, department_id, AVG(salary) FROM employees GROUP BY department_id; ERROR at line 1: ORA-00979: not a GROUP BY expressionQuery does not make sense: there is only 1 value of department_id and average salary to display for many values of last name

  10. Excluding Group Results • Use the HAVING clause to exclude set(s) of results from a GROUP BY clause (rows are grouped; group function is applied; groups matching the HAVING clause are displayed). • Example, do not display departments having 2 or less employees: SELECT department_id DEPT, MAX(salary) MAX, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 2 DEPT MAXCOUNT(*) 50 58005 60 90003 80110003 90 240003

  11. HAVING Clause Error • Cannot use the HAVING clause to rows - must reference a group function result in HAVING clause. • Example, try to exclude IT_PROG from results: SELECT department_id DEPT, MAX(salary) MAX, COUNT(*) FROM employees GROUP BY department_id HAVING job_id LIKE 'IT_PROG' • ERROR at line 4: HAVING job_id LIKE 'IT_PROG' • ORA-00979: not a GROUP BY expression

  12. HAVING Clause Notes • HAVING clause excludes result(s) for sets of rows. • Must be used with GROUP BY clause • Should only reference group results (or columns data is grouped by)

  13. Important Notes on Group Functions • You cannot use the WHERE clause to restrict groups. • You must use the HAVING clause to restrict groups. • You cannot use group functions in the WHERE clause (WHERE clause used to limit rows, not groups of rows) • Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause.

  14. Nesting Group Functions • Group functions can be nested • Example: find the highest of the average salary for each department: SELECT MAX(AVG(salary)) MAX FROM employees GROUP BY department_id MAX 19333.3333

More Related