`GROUP BY` and `HAVING`: Summarize and filter by group

A grand total can tell us how many orders there are, but not how they are distributed. If we need an amount per customer, per state or per month, we must form groups before calculating the summary.

GROUP BY defines those groups. HAVING allows you to keep only those whose aggregate result meets a condition.

The idea

All rows that share the values indicated in GROUP BY are gathered within the same group. Then we apply functions like COUNT, SUM or AVG.

WHERE and HAVING act at different times:

WHERE filters rows before grouping.
HAVING filters groups after calculating aggregates.

Syntax

SELECT
    ColumnaDeGrupo,
    COUNT(*) AS Cantidad
FROM dbo.Tabla
WHERE CondicionDeFila
GROUP BY ColumnaDeGrupo
HAVING COUNT(*) >= CantidadMinima;

Example

We want to find customers with at least two uncancelled orders:

SELECT
    c.IdCliente,
    c.Nombre,
    COUNT(*) AS CantidadPedidos
FROM dbo.Cliente AS c
INNER JOIN dbo.Pedido AS p
    ON p.IdCliente = c.IdCliente
WHERE p.Estado <> 'Cancelado'
GROUP BY
    c.IdCliente,
    c.Nombre
HAVING COUNT(*) >= 2
ORDER BY CantidadPedidos DESC;

ClientId	Name	OrderQuantity
1	Ana Torres	2

First, Diego’s canceled order is discarded. The remaining orders are then grouped by customer. Finally, HAVING keeps only Ana’s group because it contains two rows.

Why do we group identifier and name

The query shows IdCliente and Nombre, so they both appear in GROUP BY. Grouping by name alone could mix different people who share the same value.

The identifier distinguishes the client. The name is included to present it without artificial aggregation being applied to it.

`WHERE` does not replace `HAVING`

This condition cannot be written directly to WHERE:

COUNT(*) >= 2

At the time WHERE is evaluated, the groups do not yet exist. HAVING operates after GROUP BY and can evaluate the result of COUNT.

When the condition does not depend on an aggregate, it is often preferable to apply it in WHERE to reduce the rows before forming the groups.

Group without `HAVING`

If we want to observe all the states and their number of orders:

SELECT
    p.Estado,
    COUNT(*) AS CantidadPedidos
FROM dbo.Pedido AS p
GROUP BY p.Estado
ORDER BY p.Estado;

State	OrderQuantity
Canceled	1
Confirmed	2
Sent	1
Pending	1

What should be checked

Adding columns to GROUP BY changes the level of detail: the values that distinguish each row in the result. A summary per customer is not equivalent to one per customer and date; the latter can produce several groups for the same person.

We must also review preceding joins. If each order has several items and we group after joining PedidoDetalle, COUNT(*) will count items, not orders. In that case we may need COUNT(DISTINCT p.IdPedido), which counts distinct identifiers, or an intermediate query: an earlier result used as input for the next calculation.