GROUP BY and HAVING

Row filters, group filters and result grain.

UnderstandStudy an examplePractise

Understand the concept

GROUP BY collects rows sharing a key combination. A result row now represents that group, and non-grouped SELECT columns must be aggregated. Adding an extra grouping column can unintentionally change the result grain.

WHERE filters rows before grouping; HAVING filters the resulting groups using aggregate conditions. For customers with at least two paid orders, payment status belongs in WHERE and the order-count condition belongs in HAVING.

NULL grouping keys form one group. Keep the distinction between COUNT(*) and COUNT(Email) inside groups too. Before grouping, state which source rows qualify and what each result row represents.

WHEN TO USE IT

Use it for customer, city, category or period reports.

COMMON PITFALL

Putting SUM conditions in WHERE or adding arbitrary GROUP BY columns to silence an error.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT City, COUNT(*) AS Customers FROM lab.Customer GROUP BY City;

Istanbul has 3 customers; each other city has 1.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Find cities containing at least two customers.

Show the solution and explanation

Worked solution

SELECT City, COUNT(*) AS Customers FROM lab.Customer GROUP BY City HAVING COUNT(*) >= 2;

Only Istanbul, with 3 customers.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.