Aggregation: GROUP BY, HAVING, COUNT, SUM

Shell `find`/`aggregate` is the raw query, the aggregation pipeline is server-side filter-group-compute, and Spring Data gives derived methods, `@Query`, or a `MongoTemplate` custom repo for anything complex.

Extract year and month from the date, `GROUP BY` both, `SUM` the amount, and `ORDER BY` year, month for a chronological revenue report.

`GROUP BY customer_id` and `SUM(amount)` for total spend per customer – use `LEFT JOIN` with `COALESCE(SUM, 0)` to include customers who have never spent.

`GROUP BY` the columns that define a duplicate, then `HAVING COUNT(*) > 1` – because you cannot filter an aggregate in `WHERE`.

Conditional aggregation uses `SUM(CASE WHEN cond THEN 1 ELSE 0 END)` to count or pivot per group in a single pass, replacing self-joins and multiple subqueries.

Group by the customer and use `HAVING COUNT(CASE WHEN status='A') > 0 AND COUNT(CASE WHEN status='B') > 0` – both states proven in a single pass.

NULL means unknown so comparisons with it are never true, `WHERE` cannot see `SELECT` aliases, and aggregate/order clauses have strict rules you must respect.

`COUNT(*)` counts rows, `COUNT(column)` counts non-NULL values in that column, and other aggregates like SUM and AVG also silently skip NULLs.

PIVOT turns rows into columns, and `SUM(CASE WHEN ... END)` with GROUP BY does the same thing portably on any database.

`WHERE` filters rows before grouping and cannot use aggregates; `HAVING` filters groups after aggregation and is where aggregate conditions live.