FIT2094 Lecture Notes - Lecture 9: Aggregate Function, Database
31 May 2018
School
Department
Course
Professor
Lec 9: SQL10!
Aggregate Functions • COUNT,MAX,MIN,SUM,AVG !
GROUP BY !
▪ If a GROUP BY clause is used with aggregate function, the DBMS will apply the aggregate
function to the different groups defined in the clause rather than all rows. !
SELECT avg(mark) SELECT unit_code, avg(mark) FROM enrolment; FROM enrolment !
GROUP BY unit_code; !
HAVING clause !
▪ It is used to put a condition or conditions on the groups defined by GROUP BY clause. !
HAVING and WHERE clauses !
SELECT unit_code, count(*) FROM enrolment$
WHERE mark IS NULL GROUP BY unit_code HAVING count(*) > 1; !
•The WHERE clause is applied to ALL rows in the table. $
•The HAVING clause is applied to the groups defined by the GROUP BY clause. $
•The order of operations performed is FROM, WHERE, GROUP BY, HAVING and then
ORDER BY. $
•On the above example, the logic of the process will be: !
•All rows where mark is NULL are retrieved. (due to the WHERE clause) $
•The retrieved rows then are grouped into different unit_code. $
•If the number of rows in a group is greater than 1, the unit_code and the total is
displayed. (due to the HAVING clause) $
Subqueries !
• Query within a query.$
"Find all students whose mark is higher than the !
average mark of all enrolled students" !
SELECT *$
FROM enrolment$
WHERE mark > (SELECT avg (mark) !
FROM enrolment ); !
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
Group by: if a group by clause is used with aggregate function, the dbms will apply the aggregate function to the di erent groups de ned in the clause rather than all rows. Select avg(mark) select unit_code, avg(mark) from enrolment; from enrolment. Having clause: it is used to put a condition or conditions on the groups de ned by group by clause. Where mark is null group by unit_code having count(*) > 1; The where clause is applied to all rows in the table. The having clause is applied to the groups de ned by the group by clause. The order of operations performed is from, where, group by, having and then. On the above example, the logic of the process will be: All rows where mark is null are retrieved. (due to the where clause) The retrieved rows then are grouped into di erent unit_code.
