HAVING Clause

HAVING filters groups after aggregation.

WHERE filters rows before aggregation.

Core Concepts

flowchart TD
  A[Rows] --> B[WHERE filter (optional)]
  B --> C[GROUP BY]
  C --> D[Aggregate calculations]
  D --> E[HAVING filter]
  E --> F[Final grouped result]

Clause	Applied to	Typical use
WHERE	Individual rows	Pre-filter source data
HAVING	Groups	Filter by aggregate results

WHERE Example

SELECT category, SUM(amount) AS total_amount
FROM payments_agg
WHERE amount >= 70
GROUP BY category
ORDER BY category;

category	total_amount
cloud	70.0
tools	300.0

HAVING Example

SELECT category, SUM(amount) AS total_amount
FROM payments_agg
GROUP BY category
HAVING SUM(amount) >= 200
ORDER BY category;

category	total_amount
tools	300.0

Code Examples

-- Keep only categories with at least 3 rows.
SELECT
  category,
  COUNT(*) AS payment_count,
  SUM(amount) AS total_amount
FROM payments_agg
GROUP BY category
HAVING COUNT(*) >= 3
ORDER BY category;

category	payment_count	total_amount
tools	3	300.0

-- Combine WHERE and HAVING in one query.
SELECT
  category,
  COUNT(*)    AS payment_count,
  SUM(amount) AS total_amount
FROM payments_agg
WHERE amount >= 60
GROUP BY category
HAVING SUM(amount) >= 130
ORDER BY category;

category	payment_count	total_amount
cloud	2	130.0
tools	3	300.0

SQLite-Specific Nuances

SQLite Nuance

SQLite follows standard SQL processing order: row filters (WHERE) happen before grouping, group filters (HAVING) happen after aggregation.

Common Pitfalls / Best Practices

Pitfall

Using HAVING for non-aggregate row filters, which makes queries harder to read and may do extra work.

Best Practice

Use WHERE for row-level conditions and reserve HAVING for aggregate-based conditions.

Quick Challenge

From payments_agg, return categories whose average amount is at least 90.

View Solution

SELECT category, AVG(amount) AS avg_amount
FROM payments_agg
GROUP BY category
HAVING AVG(amount) >= 90
ORDER BY category;

category	avg_amount
tools	100.0