SQL GROUP BY and HAVING Explained with Examples

Aggregate functions on their own give you one answer for the whole table. Most business questions need one answer per group, such as revenue per category or orders per city. GROUP BY does that.

It splits the rows into groups first, then runs the aggregate function once inside each group.

Revenue per Category

Here is a course_sales table for an online learning platform.

SELECT category,
       COUNT(*)    AS sales,
       SUM(amount) AS total_revenue
FROM course_sales
GROUP BY category
ORDER BY total_revenue DESC;

Output:

Code Explanation:

• GROUP BY category sorts the seven rows into four buckets, one for each category.
• COUNT(*) and SUM(amount) then run separately inside every bucket.
• Every column in SELECT must either appear in GROUP BY or sit inside an aggregate function. Here, we group by category, and aggregate the other two.
• ORDER BY total_revenue DESC ranks the groups from highest to lowest. The sort runs last, so it can use the alias.

A bar chart makes the gap between the categories easy to see. Data Science brings in more than twice as much as any other category.

Figure 1: SQL GROUP BY example showing total revenue by course category in a bar chart

Filtering Groups with HAVING

WHERE filters individual rows before any grouping happens. It cannot see totals because they don't exist yet at that stage. To filter on a group’s total, use HAVING, which runs after the groups are built.

SELECT category, SUM(amount) AS total_revenue
FROM course_sales
GROUP BY category
HAVING SUM(amount) > 10000;

Output:

Code Explanation:

• The groups and their totals are built first, exactly as before.
• HAVING then removes any group whose total is below 10000, dropping Marketing at 5000.

WHERE or HAVING

A simple way to choose: if the condition is about a single row, use WHERE. If it is about a total, count or average of a group, use HAVING. You can use both in one query, and the database applies WHERE first, then groups, then applies HAVING.