GROUP BY in SQL

The whole point of GROUP BY is to stop treating your table as a list of individual rows and start treating it as a collection of groups. When you have a column where the same value appears many times — like a customer ID or a payment mode — GROUP BY bundles all rows that share that value into a single group. Once the rows are grouped, you can apply an aggregate function (COUNT, SUM, AVG, MAX, MIN) to each group to get one summary result per group.

For example, the SALE table in the CARSHOWROOM database has six rows. Several customers appear more than once (C0001 bought two cars, C0002 bought two cars). Without GROUP BY, a SELECT just lists every row. With GROUP BY CustID, the database first collects all rows for C0001 into one group, all rows for C0002 into another, and so on. Then COUNT(*) tells you how many rows are in each group — that is, how many cars each customer purchased.

---

The HAVING clause — filtering groups, not rows

WHERE filters rows before grouping. HAVING filters groups after grouping. This is a critical distinction.

If you want to see only those customers who bought more than one car, you cannot write WHERE COUNT(*) > 1 — that would be illegal because WHERE cannot see the result of an aggregate. Instead, you write:

SELECT CustID, COUNT(*) FROM SALE
GROUP BY CustID
HAVING COUNT(*) > 1;

The output shows only C0001 and C0002, each with a count of 2. The customers who bought only one car (C0003, C0004) are excluded because their groups do not satisfy the HAVING condition.

---

Ordering grouped results

You can combine GROUP BY with ORDER BY. The ORDER BY clause comes last, after HAVING (if present). GROUP BY can be combined with ORDER BY to sort the grouped output, and with HAVING to filter which groups survive — Example 1.6 works through both combinations on the SALE table's PaymentMode column.

---

Summary of the clause order in a SELECT statement

When you use GROUP BY, the full sequence is: …