Software Specification KXA233 Lecture 5A Group Functions and Subqueries

Software SpecificationKXA233Lecture 5AGroup Functions and Subqueries Paul Crowther School of Computing University of Tasmania

Today... • Detail on group functions • Subqueries - writing a query for the results of a query

Aggregating Data Using Group Functions • Group functions operate on sets of rows to give one result per group • AVG • COUNT • MAX • MIN • SUM • VARIANCE

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

Examples... 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 • Group functions ignore NULL values in the column

Group Functions and NULL Values • Group functions ignore NULL values • For example AVG(sal) would calculate an average based on rows were a valid sal value was stored • The exception is COUNT(*)

NVL and Group Functions • NVL function forces group functions to include NULL values • SQL> SELECT AVG(NVL(comm,0)) 2 FROM emp; • Remember the ,0 tells what value a NULL is to take • AVG(NVL(COMM,0)--------------------------- 157.14286

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

Example • All columns in the SELECT list that are not in group functions must be in the GROUP BY clause • SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP By deptno; • DEPTNO AVG(SAL)--------------------------------------- 30 1566.6667 20 2175 10 2916.6667

Another Example • The GROUP BY column does not have to be in the SELECT list • SQL> SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno; • AVG(SAL)------------------- 2916.6667 2175 1566.6667

GROUP BY on Multiple Columns • SQL> SELECT deptno, job, SUM(sal) 2 FROM emp 3 GROUP BY deptno, job; • DEPTNO JOB SUM(SAL)----------------------------------------------------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900

Excluding Group Results:HAVING Clause • Use the HAVING clause to restrict groups • Rows are grouped • The group function is applied • Groups matching the HAVING clause are displayed • SELECT column, group_function(column)FROM table[WHERE condition][GROUP BY group_by_expression][HAVING group condition][ORDER BY column];

Example • SELECT deptno, MAX(sal)FROM empGROUP BY deptnoHAVING MAX(sal)>2900; • DEPTNO MAX(SAL)--------------------------------- 10 5000 20 3000

Example • SELECT job, SUM(sal) PAYROLLFROM empWHERE job NOT LIKE ‘SALES%’GROUP BY jobHAVING SUM(sal)>5000ORDER BY SUM(sal); • JOB PAYROLL---------------------------------ANALYST 6000MANAGER 8725

Nesting Group Functions • Display the maximum average salary • SQL> SELECT max(avg(sal)) 2 FROM emp 3 GROUP BY deptno; • MAX(AVG(SAL))------------------------- 2916.6667

Using a Subquery to Solve a Problem • Who has a greater salary than Jones? • Main QueryWhich employees have a salary greater than Jones’ salary? • SubqueryWhat is Jones’ Salary?

Subqueries • SELECT select_listFROM tableWHERE expr operator (SELECT select_list FROM table); • The subquery (inner query) executes once before the main query • The result of the subquery is used by the main query (outer query)

Example • SQL> SELECT ename 2 FROM emp 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE ename = ‘JONES’); • ENAME-----------------KINGFORDSCOTT

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

Types of Subqueries • Single-row subqueriesMain query Subquery CLERK • Multiple-row subqueriesMain query Subquery CLERK MANAGER • Multiple-column subqueriesMain query Subquery CLERK 7900 MANAGER 7698

Example…Single Row Subqueries • Returns only ONE row • Use only = > < >= <= < > • SQL> SELECT ename, job 2 FROM emp 3 WHERE job = 4 (SELECT job 5 FROM emp 6 WHERE empno = 7369) 7 AND 8 (SELECT sal 9 FROM emp 10 WHERE empno = 7876); • ENAME JOB---------------------------------------MILLER CLERK

With HAVING Clause • The Oracle Server executes subqueries first • The Oracle server returns results into the HAVING clause of the main query • SQL> SELECT deptno, MIN(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING MIN(sal) > 5 (SELECT MIN(sal) 6 FROM emp 7 WHERE deptno = 20); • DEPTNO MIN(SAL)-------------------------------------------- 10 1300 30 950

Next Week • Multiple - Row subqueries • Multiple Column subqueries • Introduction to interactive reports

Software Specification KXA233 Lecture 5A Group Functions and Subqueries