🟢 Beginner/Intermediate · Topic 2 of 7
SQL GROUP BY & Aggregate Functions
Learn to summarise and group data — the foundation of business reporting and one of the most tested SQL skills in every analyst interview.
Core Functions
Aggregate Functions
Aggregate functions collapse multiple rows into a single summary value. They are the most used SQL functions in business analytics — every dashboard, every report, every KPI uses aggregates.
| Function | What it returns | NULL behaviour |
|---|---|---|
COUNT(*) | Total rows including NULLs | Counts everything |
COUNT(col) | Non-NULL values in column | Ignores NULLs |
COUNT(DISTINCT col) | Unique non-NULL values | Ignores NULLs & duplicates |
SUM(col) | Total of all values | Ignores NULLs |
AVG(col) | Mean of all values | Ignores NULLs (not zero!) |
MIN(col) | Smallest value / earliest date | Ignores NULLs |
MAX(col) | Largest value / latest date | Ignores NULLs |
SQL — Aggregate Functions
— How many total orders?
SELECT COUNT(*) AS total_orders FROM orders;
— How many UNIQUE customers placed orders?
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;
— Total, average, min, max revenue
SELECT
SUM(order_amount) AS total_revenue,
ROUND(AVG(order_amount), 2) AS avg_order,
MIN(order_amount) AS smallest_order,
MAX(order_amount) AS largest_order
FROM orders;
Key insight: AVG ignores NULLs — it divides SUM by the count of non-NULL values, not total rows. If half your rows are NULL, AVG will be much higher than the true average. Always check for NULLs before trusting AVG.
Grouping
GROUP BY — Aggregate Per Group
GROUP BY divides rows into groups and applies aggregate functions to each group separately. This answers “total revenue PER region” or “orders PER customer” — the most common analytics questions.
SQL — GROUP BY
— Revenue by region
SELECT region, SUM(order_amount) AS revenue
FROM orders
GROUP BY region
ORDER BY revenue DESC;
— Orders and revenue per customer
SELECT
customer_id,
COUNT(*) AS total_orders,
SUM(order_amount) AS total_revenue,
ROUND(AVG(order_amount), 0) AS avg_order
FROM orders
GROUP BY customer_id
ORDER BY total_revenue DESC;
Rule: Every column in SELECT must either be in GROUP BY or be an aggregate function. If you select a non-aggregated column that isn’t in GROUP BY, you’ll get an error. This is the most common GROUP BY mistake.
Filtering Groups
HAVING — Filter After Grouping
HAVING filters groups after GROUP BY runs. It’s like WHERE but for groups. This is a very common interview topic — knowing when to use WHERE vs HAVING sets you apart.
WHERE vs HAVING: WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER grouping. Use WHERE to filter raw data. Use HAVING to filter the result of aggregations.
SQL — HAVING
— Customers with more than 5 orders (filter on GROUP result)
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;
— WHERE + HAVING: filter rows first, then filter groups
SELECT region, SUM(order_amount) AS revenue
FROM orders
WHERE order_date >= ‘2024-01-01’ — filter rows first
GROUP BY region
HAVING SUM(order_amount) > 100000 — filter groups
ORDER BY revenue DESC;
Memory trick: WHERE = before GROUP BY. HAVING = after GROUP BY. You can use both in the same query — WHERE runs first on individual rows, then GROUP BY groups them, then HAVING filters the groups.
Conditional Aggregation
CASE WHEN — Conditional Logic in SQL
SQL — CASE WHEN
— Conditional count (pivot-style in one query)
SELECT
COUNT(*) AS total_orders,
COUNT(CASE WHEN status = ‘delivered’ THEN 1 END) AS delivered,
COUNT(CASE WHEN status = ‘returned’ THEN 1 END) AS returned,
SUM(CASE WHEN status = ‘delivered’ THEN order_amount ELSE 0 END) AS delivered_revenue
FROM orders;
— Segment customers by spend
SELECT
CASE
WHEN total_spend > 50000 THEN ‘High Value’
WHEN total_spend > 10000 THEN ‘Medium Value’
ELSE ‘Low Value’
END AS customer_segment,
COUNT(*) AS customer_count
FROM customer_totals
GROUP BY customer_segment;
Interview power move: CASE WHEN inside COUNT or SUM lets you pivot data or conditionally aggregate in a single query — instead of running multiple queries. This is very impressive in interviews.
Interview Questions
Practice Questions — GROUP BY
Easy
Find the total revenue and order count for each product category.
TCSInfosys
+
Group by category, use SUM and COUNT as aggregates.
SELECT category,
COUNT(*) AS orders,
SUM(order_amount) AS revenue
FROM orders
GROUP BY category
ORDER BY revenue DESC;
COUNT(*) AS orders,
SUM(order_amount) AS revenue
FROM orders
GROUP BY category
ORDER BY revenue DESC;
Medium
Find categories where average order value exceeds ₹8,000.
SwiggyFlipkart
+
Use HAVING to filter on AVG — this cannot be done with WHERE since AVG is calculated after grouping.
SELECT category, ROUND(AVG(order_amount), 0) AS avg_order
FROM orders
GROUP BY category
HAVING AVG(order_amount) > 8000
ORDER BY avg_order DESC;
FROM orders
GROUP BY category
HAVING AVG(order_amount) > 8000
ORDER BY avg_order DESC;
Hard
In a single query, count delivered and returned orders per region, plus their revenue.
AmazonGoogle
+
Use conditional aggregation with CASE WHEN inside COUNT and SUM.
SELECT region,
COUNT(CASE WHEN status=‘delivered’ THEN 1 END) delivered_orders,
COUNT(CASE WHEN status=‘returned’ THEN 1 END) returned_orders,
SUM(CASE WHEN status=‘delivered’ THEN order_amount ELSE 0 END) revenue
FROM orders GROUP BY region;
COUNT(CASE WHEN status=‘delivered’ THEN 1 END) delivered_orders,
COUNT(CASE WHEN status=‘returned’ THEN 1 END) returned_orders,
SUM(CASE WHEN status=‘delivered’ THEN order_amount ELSE 0 END) revenue
FROM orders GROUP BY region;
Ready to practice GROUP BY with a mentor?
Book a free SQL mock session. We’ll run real groupby interview questions and give live feedback.
Book Free Session