Learning Objectives – after this chapter you will be able to:
- Group rows with
GROUP BYand summarise them with group functions. - Use DISTINCT inside group functions and know what COUNT(*) counts.
- Filter groups with
HAVINGand combine WHERE, GROUP BY, HAVING and ORDER BY correctly. - Produce sub-totals and grand totals with
ROLLUP,CUBEandGROUPING.
GROUP BY Clause
GROUP BY divides the rows of a table into groups – one group for each distinct value of the given column(s) – so that a group function can compute one value per group.
Number of students in each batch
select bcode, count(rollno) from students group by bcode;
Output
BCODE COUNT(ROLLNO) ----- ------------- b1 2 b2 3 b3 2 b4 1 b5 3
To build a grouping query, decide: (1) the table that holds the data, (2) the column(s) to group on, and (3) the group function that gives the required aggregate.
When grouping, the SELECT list may contain only the GROUP BY columns and group functions.
Group Functions
group-function ( [DISTINCT] column )| Function | Returns |
|---|---|
| SUM | Sum of the column |
| AVG | Average of the column |
| MIN / MAX | Minimum / maximum value |
| STDDEV | Standard deviation |
| VARIANCE | Variance (listed as VAR in the notes) |
| COUNT(column) | Number of not null values in the column |
| COUNT(*) | Number of rows in the group |
DISTINCT inside a group function; group function without GROUP BY
select count(rollno) from payments; -- counts every payment (duplicates too) select count(distinct rollno) from payments; -- 11 : students who paid at least once select max(fee) from courses; -- no GROUP BY: whole table is one group select ccode, max(stdate) from batches group by ccode; -- latest batch of each course
WHERE, GROUP BY on Several Columns
- WHERE is executed first: it selects the rows that are then grouped. GROUP BY follows WHERE in the statement.
- Grouping on more than one column groups by the first column and, within it, by the next, and so on.
select ccode, count(*) count from batches where months_between(sysdate, stdate) <= 3 group by ccode; -- batches started in the last 3 months select fcode, ccode, max(stdate) from batches group by fcode, ccode; -- last batch of each faculty for each course
HAVING Clause
HAVING filters groups using conditions on group functions (SUM, COUNT …) or GROUP BY expressions.
| WHERE | HAVING | |
|---|---|---|
| Filters | Individual rows | Groups |
| Group functions | Not allowed – ORA-00934: group function is not allowed here | Allowed |
| Non-group conditions | Allowed | Not allowed – ORA-00979: not a GROUP BY expression (except on GROUP BY columns) |
HAVING with WHERE and ORDER BY
select ccode from batches group by ccode having count(*) > 1; -- courses with more than one batch select ccode, count(*) from batches where months_between(sysdate, stdate) <= 6 group by ccode having count(*) > 1; -- WHERE first, then HAVING select fcode, count(*) from course_faculty group by fcode order by count(*); -- ORDER BY is last
Order of execution
1. WHEREselect rows
2. GROUP BYform groups
3. Group functionscompute per group
4. HAVINGeliminate groups
5. ORDER BYsort result
With GROUP BY, the ORDER BY clause may use only GROUP BY columns or group functions.
ROLLUP, CUBE and GROUPING
- ROLLUP(c1, c2) (Oracle8i) adds sub-totals moving right to left: totals for each c1, then the grand total.
- CUBE(c1, c2) produces all possible sub-totals: everything ROLLUP gives plus totals for each c2.
- In the extra rows the rolled-up column is NULL; GROUPING(column) returns 1 when the value was generated by ROLLUP/CUBE, so DECODE can show a label.
Readable sub-totals with GROUPING and DECODE
select decode(grouping(ccode),1,'ALL courses',ccode) ccode,
decode(grouping(fcode),1,'All faculty',fcode) fcode,
count(*) count
from batches
group by rollup(ccode, fcode); -- per course+faculty, per course, grand total
-- group by cube(ccode, fcode) -- ... plus a total for every facultyNVL can also label the NULLs (nvl(ccode,'ALL courses')), but GROUPING distinguishes a generated total from a real NULL value.
Key Takeaways
- SELECT only GROUP BY columns and group functions in a grouped query.
- WHERE filters rows before grouping; HAVING filters groups after grouping.
- COUNT(*) counts rows; COUNT(column) ignores NULLs; DISTINCT removes duplicates.
- ROLLUP = hierarchical sub-totals; CUBE = all combinations.
Chapter 8 Notes (PDF)
ch8.pdfKeys: ← → page · + − zoom · F full screen
Question / Answer (15 questions)
Moderate-level questions based on the Chapter 8 notes (PDF). Click a question to see the answer.
Answer : GROUP BY divides the rows of a table into groups based on the column(s) given – all rows with the same value form one group – so that group functions can perform operations on each group, e.g. counting the number of students in each batch by grouping STUDENTS on BCODE.
Answer : The table from which the data is to be taken, the column or columns on which the data is to be grouped, and the group function to be used to get the required aggregate.
Answer : Only the columns given in the GROUP BY clause and group functions.
Answer : SUM (sum), AVG (average), MIN (minimum), MAX (maximum), STDDEV (standard deviation), VARIANCE (variance) and COUNT (number of not-null values in a column, or number of rows with *).
Answer : COUNT(*) counts the number of rows in each group. COUNT(column) counts the number of not-null values in that column. COUNT(DISTINCT column) counts only distinct values – e.g. COUNT(DISTINCT rollno) FROM payments counts each student once even if he paid several times.
Answer : Yes. Then all the selected rows are taken as a single group and the function operates on the entire set, e.g. SELECT MAX(fee) FROM courses returns the highest course fee. SELECT COUNT(*) FROM students is therefore a valid query.
Answer : The WHERE clause is executed first and selects the rows; the selected rows are then grouped by GROUP BY. GROUP BY must follow WHERE in the statement.
Answer : Rows are first grouped by the first column, and within each such group they are grouped again by the second column, and so on – e.g. GROUP BY fcode, ccode gives one group for each faculty and course combination.
Answer : HAVING is used to select (filter) groups based on a condition that uses the result of group functions, e.g. GROUP BY ccode HAVING COUNT(*) > 1 displays the courses that have more than one batch.
Answer : WHERE checks conditions on the values of columns and expressions of individual rows and cannot use group functions (ORA-00934: group function is not allowed here). HAVING evaluates conditions based on group functions such as SUM and COUNT and cannot be used for conditions not related to groups (ORA-00979: not a GROUP BY expression), though it can use GROUP BY columns.
Answer : It selects rows using WHERE, groups them using GROUP BY, calculates the results for each group, eliminates groups using HAVING and finally orders the result using ORDER BY.
Answer : ORDER BY must be the last clause, and it should contain either a GROUP BY column or a group function, e.g. GROUP BY fcode ORDER BY COUNT(*).
Answer : ROLLUP, introduced in Oracle8i, creates sub-totals from the most detailed level to the grand total, moving from right to left in its column list. GROUP BY ROLLUP(ccode, fcode) gives counts for each course and faculty, a total for each course and a grand total.
Answer : CUBE generates the same sub-totals as ROLLUP plus all other possible sub-totals. With CUBE(ccode, fcode) it additionally gives the total number of batches taken by each faculty for all courses.
Answer : GROUPING(column) returns 1 if the column’s value in that row was generated by ROLLUP or CUBE (a sub-total row). It is combined with DECODE to display meaningful labels, e.g. DECODE(GROUPING(ccode),1,'ALL courses',ccode).
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.
- Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
- Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
- Oracle Corporation. (n.d.). Oracle Database Development Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/
- Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.
- Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson.