MySQL HAVING Clause

MySQL HAVING Clause: Filtering Grouped Results

The MySQL HAVING clause filters groups produced by GROUP BY using aggregate conditions such as COUNT(*) > 5 or SUM(total_amount) >= 500. While WHERE filters individual rows before grouping happens, HAVING filters summary groups after aggregates are computed.

Basic Syntax

Place HAVING immediately after GROUP BY and before ORDER BY:

SELECT
    group_column,
    COUNT(*) AS item_count,
    SUM(amount_column) AS total_amount
FROM table_name
WHERE row_condition
GROUP BY group_column
HAVING COUNT(*) >= 3
ORDER BY total_amount DESC;

Practical SQL Example

This query finds repeat buyers in demo_orders who have at least 2 paid orders totaling 200 dollars or more:

SELECT
    customer_id,
    COUNT(*) AS paid_orders,
    SUM(total_amount) AS lifetime_spend
FROM demo_orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2
   AND SUM(total_amount) >= 200.00
ORDER BY lifetime_spend DESC;

Common Mistakes and Best Practices

• Put Non-Aggregate Filters in WHERE, Not HAVING: Writing HAVING status = 'paid' forces MySQL to group every cancelled and pending row before discarding them. Filter WHERE status = 'paid' first so indexes narrow the table before aggregation.
• Find Duplicate Emails Cleanly: SELECT email, COUNT(*) AS dupes FROM demo_customers GROUP BY email HAVING COUNT(*) > 1; is the fastest way to audit duplicate records before adding a UNIQUE constraint.
• Avoid Heavy Expressions Inside HAVING: Keep HAVING focused on clear aggregate thresholds such as COUNT(*), SUM(), or AVG() rather than complex unindexed calculations.

Frequently Asked Questions

Can I use HAVING without GROUP BY in MySQL?

Yes, MySQL treats the whole table as one group if you omit GROUP BY, though this is rare outside aggregate-only checks.

Can HAVING reference a SELECT column alias?

Yes, unlike WHERE, MySQL allows HAVING to reference aggregate aliases such as HAVING lifetime_spend >= 200.00.

Why does WHERE COUNT(*) > 1 throw an invalid use of group function error?

Because WHERE runs row by row before GROUP BY computes aggregates; move aggregate comparisons into HAVING.