SQL GROUP BY and HAVING: A Complete Tutorial

Introduction

Aggregation is the heart of analytics. Almost every question a business asks — "How much did we sell last month?", "Which city has the most customers?", "What is our average order value?" — is really a request to group rows into buckets and calculate a number for each bucket. In SQL, that is the job of GROUP BY, and its close partner HAVING.

GROUP BY and HAVING look simple on the surface, but they are also the source of most beginner errors and the topic that separates analysts who understand SQL from those who only recognize it. If you are working through a data analytics course in Bangalore or teaching yourself, this tutorial will give you a clear, example-driven understanding of both clauses and the aggregation logic behind them. If you are brand new to query writing, you may also want to review SQL basics for analysts or check out our complete SQL guide.

What GROUP BY Does

GROUP BY collapses multiple rows into a single row per unique value in the columns you name. It is how you turn a raw transaction table into a summary.

Imagine an orders table with a million rows, each representing one purchase. If you write:

SELECT city, COUNT(*) AS total_orders
FROM orders
GROUP BY city;

The database groups every row by its city value, counts the rows in each group, and returns one row per city. A million rows become maybe 50 rows — one per city — with the count of orders in each. That collapse from raw rows to summary rows is what aggregation is.

You can group by multiple columns. GROUP BY city, product_category will produce one row for each unique combination of city and product category. Every non-aggregated column in your SELECT list must appear in the GROUP BY, or the query will fail (or worse, return unpredictable results in some dialects).

The Aggregate Functions You Will Use Every Day

GROUP BY on its own does nothing useful. You pair it with an aggregate function that computes a value for each group. The five aggregates every analyst uses constantly are:

COUNT — count of rows in the group. COUNT(*) counts all rows including NULLs; COUNT(column) counts only non-NULL values in that column; COUNT(DISTINCT column) counts unique non-NULL values.

SUM — total of a numeric column. Used for revenue, quantity, units.

AVG — average of a numeric column. Used for average order value, average session duration.

MIN and MAX — smallest and largest value in the group. Used for first-purchase-date, highest-priced product, latest login.

A single GROUP BY query can use several aggregates at once:

SELECT city,
COUNT(*) AS total_orders,
COUNT(DISTINCT customer_id) AS unique_customers,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value,
MAX(order_date) AS most_recent_order
FROM orders
GROUP BY city;

This one query produces a rich per-city summary. Learning to bundle several aggregates into one query is a habit any good data analytics course in Bangalore will encourage — it is more efficient than writing five separate queries.

The Golden Rule of GROUP BY

Here is the rule that trips up nearly every beginner: every column in your SELECT list must either appear in the GROUP BY clause or be inside an aggregate function. There is no third option.

This works:
SELECT city, COUNT(*) FROM orders GROUP BY city;

This will fail or return wrong results:
SELECT city, customer_id, COUNT(*) FROM orders GROUP BY city;

The customer_id column is neither in GROUP BY nor inside an aggregate. The database does not know which customer_id to show, since each city group contains many different customers. MySQL will sometimes silently pick one and return misleading results; PostgreSQL and most modern databases will throw an error.

Once you internalize this rule, GROUP BY errors become easy to diagnose.

HAVING: Filtering Groups, Not Rows

WHERE filters rows before they are grouped. HAVING filters groups after aggregation. That is the entire distinction, and it is the single most common SQL interview question.

Suppose you want cities with more than 1000 orders. This is wrong:
SELECT city, COUNT(*) AS total_orders
FROM orders
WHERE COUNT(*) > 1000
GROUP BY city;

WHERE runs before GROUP BY, so COUNT(*) does not exist yet. This is the correct version:
SELECT city, COUNT(*) AS total_orders
FROM orders
GROUP BY city
HAVING COUNT(*) > 1000;

HAVING sees the aggregated result and filters based on it. You can combine WHERE and HAVING in the same query — WHERE narrows the rows before grouping, HAVING narrows the groups after:
SELECT city, COUNT(*) AS total_orders, SUM(amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY city
HAVING SUM(amount) > 500000;

This says: consider only completed orders, group by city, and return only cities where total revenue exceeds 5 lakh. Understanding this order of operations is the biggest single conceptual leap in SQL basics, and it comes up in every data analytics course in Bangalore for good reason.

GROUP BY with Multiple Columns

Grouping by multiple columns creates one row per unique combination. This is how you build cross-tabs and dimensional summaries:
SELECT city, product_category, SUM(amount) AS revenue
FROM orders
GROUP BY city, product_category
ORDER BY city, revenue DESC;

If you have 10 cities and 5 product categories, you get up to 50 rows — one per city-category pair. This is the raw material for a dashboard that lets a user filter revenue by city and by category.

Order matters in ORDER BY but not in GROUP BY — the result is the same regardless of the column order in GROUP BY. But choosing a sensible order in your SELECT and ORDER BY makes the output much easier to read.

Combining GROUP BY with Joins

In real analytics, you almost always GROUP BY across joined tables. For a deeper look at joining tables, read our guide on SQL joins explained. Here is a realistic example — revenue per customer city, joining customers to orders:

SELECT c.city, SUM(o.amount) AS total_revenue, COUNT(DISTINCT o.customer_id) AS active_customers
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.city
ORDER BY total_revenue DESC;

The query joins the two tables, filters to 2026 orders, groups by the customer's city, and returns revenue and unique-customer counts per city. This pattern — join, filter, group, aggregate, order — is the shape of most reports you will build as a working analyst.

Common Mistakes with GROUP BY and HAVING

Beyond the WHERE-vs-HAVING confusion, three mistakes come up repeatedly.

Grouping by too few columns. If your SELECT includes a customer name but you only GROUP BY customer_id, the query may run in MySQL but return unpredictable names. Group by both, or wrap the name in MIN() or MAX() (they are the same when the group has one customer).

Forgetting DISTINCT inside COUNT. COUNT(*) counts rows, not unique things. If you want unique customers, use COUNT(DISTINCT customer_id). Confusing these is the fastest way to get "why did our customer count double this month?" complaints from stakeholders.

Using HAVING when WHERE would be faster. HAVING evaluates after aggregation. If your filter can be applied at the row level (like status = 'completed'), use WHERE — it is faster because it reduces the rows before grouping.

Careful attention to these details is what a well-run data analytics course in Bangalore will train into you, because they are the difference between reports that are correct and ones that quietly are not.

Real Business Questions Answered with GROUP BY

Here are the everyday analyst questions that GROUP BY solves, and the query patterns behind them.

Daily active users: GROUP BY DATE(event_time), COUNT(DISTINCT user_id).

Monthly recurring revenue: GROUP BY DATE_TRUNC('month', order_date), SUM(amount).

Top 10 products by revenue: GROUP BY product_id, ORDER BY SUM(amount) DESC, LIMIT 10.

Average session duration by device type: GROUP BY device_type, AVG(session_seconds).

High-value customers (spent over 1 lakh): GROUP BY customer_id, HAVING SUM(amount) > 100000.

Every one of these is a report a business will ask for at some point, and GROUP BY is what makes them possible.

Final Thoughts

GROUP BY and HAVING are how you turn transactional data into business insight. Master the golden rule (every SELECT column must be aggregated or grouped), understand that HAVING filters groups while WHERE filters rows, and practice bundling multiple aggregates into a single well-structured query. Once these feel natural, you can answer 80% of the analytical questions a business will ever ask you — which is exactly the point where SQL stops feeling like a language and starts feeling like a superpower. Keep practicing on real datasets, and if you want structured feedback, a good data analytics course in Bangalore with hands-on labs will accelerate the process considerably.