SQL Aggregations and Grouping

Raw data is just a list of rows. To answer business questions — “What is the average order value?” or “Which city has the most customers?” — you need to aggregate.

Aggregation is the process of combining multiple rows into a single summary value.

This chapter covers aggregate functions, grouping, filtering groups, and advanced OLAP features.

---

Learning Objectives

After completing this chapter, you will be able to:

• Use the five core aggregate functions.
• Write GROUP BY queries correctly.
• Filter groups with HAVING.
• Use DISTINCT with aggregates.
• Understand NULL behavior in aggregations.
• Use GROUPING SETS, ROLLUP, and CUBE.
• Answer interview questions about aggregation.

---

Sample Data

All examples use this Orders table:

CREATE TABLE Orders (
    Order_ID INT PRIMARY KEY,
    Customer VARCHAR(50),
    City VARCHAR(50),
    Product VARCHAR(50),
    Amount DECIMAL(10,2),
    Quantity INT,
    Order_Date DATE
);

Order_ID	Customer	City	Product	Amount	Quantity	Order_Date
1	Rahul	Mumbai	Laptop	50000	1	2024-01-15
2	Priya	Delhi	Phone	15000	2	2024-01-16
3	Amit	Mumbai	Tablet	25000	1	2024-01-17
4	Rahul	Mumbai	Phone	15000	1	2024-02-01
5	Priya	Delhi	Laptop	50000	1	2024-02-05
6	Sneha	Bangalore	Tablet	25000	2	2024-02-10
7	Amit	Mumbai	Laptop	50000	1	2024-03-01

---

Core Aggregate Functions

SQL provides five standard aggregate functions.

Function	Purpose	Example
COUNT()	Counts number of rows	COUNT(*) — total rows
SUM()	Sums numeric values	SUM(Amount)
AVG()	Average of numeric values	AVG(Amount)
MAX()	Maximum value	MAX(Amount)
MIN()	Minimum value	MIN(Amount)

COUNT Examples

-- Count all rows
SELECT COUNT(*) FROM Orders;  -- Result: 7

-- Count non-NULL values in a column
SELECT COUNT(Customer) FROM Orders;  -- Result: 7

-- Count distinct values
SELECT COUNT(DISTINCT Customer) FROM Orders;  -- Result: 4

SUM and AVG Examples

-- Total revenue
SELECT SUM(Amount) AS Total_Revenue FROM Orders;  -- 230000

-- Average order value
SELECT AVG(Amount) AS Avg_Order FROM Orders;  -- 32857.14

-- Average quantity per order
SELECT AVG(Quantity) AS Avg_Qty FROM Orders;  -- 1.29

MAX and MIN Examples

-- Highest and lowest order amounts
SELECT MAX(Amount) AS Highest, MIN(Amount) AS Lowest FROM Orders;
-- Result: Highest = 50000, Lowest = 15000

-- Most items in a single order
SELECT MAX(Quantity) FROM Orders;  -- 2

---

GROUP BY

GROUP BY groups rows that have the same values in specified columns. Aggregates are applied to each group independently.

Basic Syntax

SELECT column, aggregate_function(column)
FROM table
WHERE condition
GROUP BY column;

Example 1: Total Revenue by City

SELECT City, SUM(Amount) AS Total
FROM Orders
GROUP BY City;

City	Total
Mumbai	140000
Delhi	65000
Bangalore	25000

Example 2: Average Order Value by Customer

SELECT Customer, AVG(Amount) AS Avg_Order
FROM Orders
GROUP BY Customer;

Customer	Avg_Order
Rahul	32500.00
Priya	32500.00
Amit	37500.00
Sneha	25000.00

Example 3: Total Quantity Sold per Product

SELECT Product, SUM(Quantity) AS Total_Qty
FROM Orders
GROUP BY Product;

Product	Total_Qty
Laptop	3
Phone	3
Tablet	3

---

GROUP BY with Multiple Columns

You can group by multiple columns to get more granular summaries.

SELECT City, Product, SUM(Amount) AS Total
FROM Orders
GROUP BY City, Product;

City	Product	Total
Mumbai	Laptop	100000
Mumbai	Phone	15000
Mumbai	Tablet	25000
Delhi	Laptop	50000
Delhi	Phone	15000
Bangalore	Tablet	25000

This shows revenue for each product within each city.

---

Common GROUP BY Mistake

All columns in the SELECT clause must either be in GROUP BY or be wrapped in an aggregate function.

-- WRONG: Customer is not in GROUP BY and not aggregated
SELECT Customer, City, SUM(Amount)
FROM Orders
GROUP BY City;

-- RIGHT: Customer is in GROUP BY
SELECT Customer, City, SUM(Amount)
FROM Orders
GROUP BY Customer, City;

-- ALSO RIGHT: Customer is aggregated
SELECT City, COUNT(DISTINCT Customer) AS Customer_Count
FROM Orders
GROUP BY City;

---

HAVING

HAVING filters groups after aggregation.

WHERE filters rows before grouping. HAVING filters groups after grouping.

Example

Find cities with total revenue greater than 50,000.

SELECT City, SUM(Amount) AS Total
FROM Orders
GROUP BY City
HAVING SUM(Amount) > 50000;

City	Total
Mumbai	140000
Delhi	65000

WHERE + GROUP BY + HAVING

Find cities where Laptop sales exceed 40,000.

SELECT City, SUM(Amount) AS Laptop_Sales
FROM Orders
WHERE Product = 'Laptop'
GROUP BY City
HAVING SUM(Amount) > 40000;

Execution order: WHERE (filter to laptops) → GROUP BY city → HAVING (keep cities with > 40000 laptop sales).

---

WHERE vs HAVING

Aspect	WHERE	HAVING
When it applies	Before GROUP BY	After GROUP BY
Can use aggregates	No	Yes
Filters	Individual rows	Groups
Example	WHERE Amount > 10000	HAVING SUM(Amount) > 50000

---

DISTINCT with Aggregates

DISTINCT inside an aggregate counts only unique values.

-- Total customers
SELECT COUNT(DISTINCT Customer) FROM Orders;  -- 4

-- Cities with at least one order
SELECT COUNT(DISTINCT City) FROM Orders;  -- 3

Without DISTINCT:

SELECT COUNT(Customer) FROM Orders;  -- 7 (counting every row)

---

NULL Behavior in Aggregates

Function	NULL Behavior
COUNT(*)	Counts all rows, including NULLs
COUNT(column)	Ignores NULLs in that column
SUM(column)	Ignores NULLs (treats as 0)
AVG(column)	Ignores NULLs in the average
MAX/MIN(column)	Ignores NULLs

-- If Amount has a NULL value:
SELECT AVG(Amount) FROM Orders;  -- Average of non-NULL amounts only
SELECT COUNT(*) FROM Orders;     -- Total rows including NULL
SELECT COUNT(Amount) FROM Orders;  -- Non-NULL amounts only

---

Advanced Grouping Features

These features allow multi-dimensional aggregation in a single query.

GROUPING SETS

Allows you to specify multiple groupings in one query.

SELECT City, Product, SUM(Amount) AS Total
FROM Orders
GROUP BY GROUPING SETS (
    (City, Product),  -- detailed level
    (City),           -- subtotal by city
    (Product),        -- subtotal by product
    ()                -- grand total
);

ROLLUP

Generates hierarchical subtotals from most detailed to grand total.

SELECT City, Product, SUM(Amount) AS Total
FROM Orders
GROUP BY ROLLUP (City, Product);

This generates:

• Total for (Mumbai, Laptop), (Mumbai, Phone), etc.
• Subtotal for Mumbai.
• Subtotal for Delhi.
• …
• Grand total.

Useful for reporting: Year → Quarter → Month → Total.

CUBE

Generates all possible combinations of groupings.

SELECT City, Product, SUM(Amount) AS Total
FROM Orders
GROUP BY CUBE (City, Product);

This includes every combination: (City, Product), (City), (Product), ().

CUBE generates 2^n grouping sets (where n is the number of columns). For 3 columns, that is 8 groupings.

---

Aggregation Best Practices

Practice	Why
Always use table aliases	Orders o instead of Orders
Always use column aliases	SUM(Amount) AS Total
Use COUNT(*) for row counts	Fastest, includes NULLs
Use COUNT(DISTINCT) for unique counts	Correct for unique value analysis
Remember NULLs	Aggregates ignore NULLs unless using COUNT(*)
Test HAVING without it first	Run the GROUP BY query, then add HAVING

---

Interview Deep Dive

---

Key Takeaways

• Five aggregate functions: COUNT, SUM, AVG, MAX, MIN.
• GROUP BY creates groups; aggregates apply per group.
• WHERE filters before grouping; HAVING filters after.
• All non-aggregated columns in SELECT must be in GROUP BY.
• COUNT(*) includes NULLs; COUNT(column) excludes them.
• DISTINCT inside aggregates counts unique values.
• GROUPING SETS, ROLLUP, and CUBE enable multi-dimensional aggregation.
• NULLs are ignored by most aggregates except COUNT(*).