What is Grouping in Oracle SQL? With Examples

Grouping in Oracle

Rows with the same values in designated columns can be grouped into summary rows using the GROUP BY clause. This is especially helpful when you wish to apply group (or aggregate) functions, like AVG, MAX, MIN, SUM, COUNT, or SUM, to these groups instead of the full set of rows.

When a group function is used in conjunction with a column name in the SELECT clause, the GROUP BY clause must also contain that column name. An Oracle error message stating “ORA-00937: not a single-group group function” will appear if a column that is not used in an aggregate function is included in the SELECT list but left out of the GROUP BY clause. This happens as a result of the GROUP BY clause explicitly defining groups of rows, which aggregate functions are intended to work on rather than individual rows.

While COUNT is a special situation, aggregate functions typically disregard NULL values when doing computations. In the given column, COUNT(expression) will count all non-NULL values; in other columns, COUNT(*) will count all rows, including those that contain NULL values. If NULL values appear in the grouping column, they are grouped together by the GROUP BY clause, which handles them similarly to any other value.

This can be illustrated using a BOOKSHELF table.

Code Example: Creating Table and Inserting Values

First, we make a table called BOOKSHELF and add some sample data to it:

CREATE TABLE BOOKSHELF (
    Title VARCHAR2(100) PRIMARY KEY,
    CategoryName VARCHAR2(20),
    Rating NUMBER(2,1)
);

INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('The Hobbit', 'ADULTFIC', 4.5);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Lord of the Rings', 'ADULTFIC', 5.0);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('1984', 'ADULTFIC', 4.0);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Brave New World', 'ADULTFIC', 4.2);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Pride and Prejudice', 'ADULTFIC', 3.8);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('To Kill a Mockingbird', 'ADULTFIC', 4.7); -- Total 6 ADULTFIC

INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Sapiens', 'ADULTNF', 4.8);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Atomic Habits', 'ADULTNF', 4.6);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Cosmos', 'ADULTNF', 4.9);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Educated', 'ADULTNF', 4.5);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('The Power of Habit', 'ADULTNF', 4.2);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Thinking, Fast and Slow', 'ADULTNF', 4.7);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Guns, Germs, and Steel', 'ADULTNF', 4.4);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Becoming', 'ADULTNF', 4.1);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('A Brief History of Time', 'ADULTNF', 4.3);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('The Immortal Life of Henrietta Lacks', 'ADULTNF', 4.0); -- Total 10 ADULTNF

INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Merriam-Webster Dictionary', 'ADULTREF', 3.0);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Roget''s Thesaurus', 'ADULTREF', 3.5);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Encyclopedia Britannica', 'ADULTREF', 2.5);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('The World Almanac', 'ADULTREF', 3.2);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Gardening for Dummies', 'ADULTREF', 3.1);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Cooking Basics', 'ADULTREF', 3.3); -- Total 6 ADULTREF

INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Charlotte''s Web', 'CHILDRENFIC', 4.9);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Harry Potter and the Sorcerer''s Stone', 'CHILDRENFIC', 5.0);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('The Cat in the Hat', 'CHILDRENFIC', 3.5);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Where the Wild Things Are', 'CHILDRENFIC', 4.0);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Green Eggs and Ham', 'CHILDRENFIC', 3.7); -- Total 5 CHILDRENFIC

INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('The Magic School Bus', 'CHILDRENNF', 3.0); -- Total 1 CHILDRENNF

INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Brown Bear, Brown Bear', 'CHILDRENPIC', 3.9);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('The Very Hungry Caterpillar', 'CHILDRENPIC', 4.1);
INSERT INTO BOOKSHELF (Title, CategoryName, Rating) VALUES ('Pat the Bunny', 'CHILDRENPIC', 2.0); -- Total 3 CHILDRENPIC

COMMIT;

Code Example: Applying GROUP BY

To count the number of books in each category, we use GROUP BY CategoryName:

SELECT CategoryName, COUNT(*) AS NumberOfBooks
FROM BOOKSHELF
GROUP BY CategoryName;

Output:

CATEGORYNAME         NUMBEROFBOOKS
-------------------- -------------
ADULTFIC                         6
ADULTNF                         10
ADULTREF                         6
CHILDRENFIC                      5
CHILDRENNF                       1
CHILDRENPIC                      3

This output displays the number of books in each category together with each unique CategoryName.

Filtering Groups with the HAVING Clause

Similar to a WHERE clause, the HAVING clause applies its logical criteria to the output of aggregate functions on groups of rows instead of individual rows. You can use criteria that incorporate aggregated data to filter out entire groups.

Code Example: Applying HAVING

Let’s refine our previous query to only show categories that have more than 5 books:

SELECT CategoryName, COUNT(*) AS NumberOfBooks
FROM BOOKSHELF
GROUP BY CategoryName
HAVING COUNT(*) > 5;

Output:

CATEGORYNAME         NUMBEROFBOOKS
-------------------- -------------
ADULTFIC                         6
ADULTNF                         10
ADULTREF                         6

The use of the HAVING clause in filtering grouped results is seen here, as only the categories with more than five books are shown.

Distinguishing WHERE from HAVING

Writing effective queries requires an understanding of the distinction between the WHERE and HAVING clauses:

1. WHERE Clause: Sorts individual rows before grouping them. It is unable to have aggregate functions.
2. HAVING Clause: Following the application of aggregate functions to the groups, the HAVING clause filters the output of GROUP BY clauses. It may have aggregate functions.

These clauses are processed in a crucial order for performance: WHERE clauses are run first, followed by GROUP BY and HAVING. As fewer rows will need to be processed by the GROUP BY and HAVING operations, removing as many rows as feasible using WHERE clauses prior to grouping will greatly speed up query performance. Better performance will usually result from rephrasing a condition in HAVING as a WHERE condition, particularly if it does not contain an aggregate function.

Code Example: WHERE and HAVING Combined

Let’s find categories where the average rating is greater than 3.5, but only considering books with a rating greater than 1, and then order the results:

SELECT CategoryName, COUNT(*) AS NumberOfBooks, AVG(Rating) AS AverageRating
FROM BOOKSHELF
WHERE Rating > 1 -- Filters individual rows before grouping
GROUP BY CategoryName
HAVING AVG(Rating) > 3.5 -- Filters groups based on the aggregate condition
ORDER BY NumberOfBooks DESC;

Output:

CATEGORYNAME         NUMBEROFBOOKS AVERAGE_RATING
-------------------- ------------- --------------
ADULTNF                         10            4.4
ADULTFIC                         6           4.36
CHILDRENFIC                      5            4.22

WHERE Rating > 1 initially eliminates any books with a rating of 1 or lower in this example. The remaining rows are then sorted by CategoryName, and each group’s COUNT(*) and AVG(Rating) are determined. Lastly, HAVING AVG(Rating) > 3.5 filters these groups to display only those with an average rating higher than 3.5.

Advanced Grouping Features

Oracle has even more advanced grouping features, such ROLLUP and CUBE, which expand on the GROUP BY clause to provide grand totals and subtotals in a single SQL statement.

• ROLLUP produces a grand total as well as subtotals for a hierarchy of columns.
• CUBE creates subtotals, including a grand total, for every possible combination of the designated grouping columns.

Rows expressing subtotals or grand totals (superaggregate rows, which sometimes utilise NULL to symbolise the “all values” for a category) are distinguished from those representing conventional grouped data using the GROUPING function. When the output contains a NULL, it indicates a superaggregate value; otherwise, it returns 0. The GROUPING_ID function makes filtering in queries with a lot of grouping expressions easier by returning a number that corresponds to the GROUPING bit vector.

GROUP BY can be thought of as arranging your belongings into distinct boxes according to their labels (column values). Based on some measurement of their contents (such as the overall weight or the average quality of objects inside), HAVING then allows you to choose which entire boxes you want to keep, whereas WHERE would have allowed you to discard individual items before they were even placed into any boxes.