SQL HAVING: Filter Grouped Aggregate Results

The SQL HAVING clause filters grouped or aggregate results. Use it when the condition depends on values such as COUNT(), SUM(), AVG(), MIN() or MAX().

SELECT category,
       type,
       COUNT(*) AS total
FROM documents
GROUP BY category, type
HAVING COUNT(*) < 3;

This groups the documents table by category and type, then keeps only groups containing fewer than three rows.

SQL HAVING Syntax Top ↑

SELECT group_column,
       aggregate_function(column_name) AS aggregate_alias
FROM table_name
WHERE row_condition
GROUP BY group_column
HAVING aggregate_condition
ORDER BY group_column;

The normal logical flow is: filter source rows with WHERE, form groups with GROUP BY, calculate aggregates, filter those groups with HAVING, and finally sort the result with ORDER BY.

Sample Documents Table Top ↑

The original Plus2net example uses this documents table:

HAVING with COUNT() Top ↑

First group the records by category and type:

SELECT category,
       type,
       COUNT(*) AS total
FROM documents
GROUP BY category, type
ORDER BY category, type;

Now keep only groups with fewer than three rows:

SELECT category,
       type,
       COUNT(*) AS total
FROM documents
GROUP BY category, type
HAVING COUNT(*) < 3
ORDER BY category, type;

The Management / Book group is excluded because its count is 3.

WHERE vs HAVING Top ↑

The older version of this page showed an invalid query by putting WHERE after GROUP BY. The deeper issue is not only clause order: WHERE cannot filter a result based on an aggregate that has not yet been calculated.

This is wrong:

-- Wrong: aggregate filtering belongs in HAVING
SELECT category,
       type,
       COUNT(*) AS total
FROM documents
WHERE COUNT(*) < 3
GROUP BY category, type;

Use HAVING for the aggregate condition:

SELECT category,
       type,
       COUNT(*) AS total
FROM documents
GROUP BY category, type
HAVING COUNT(*) < 3;

Use WHERE and HAVING Together Top ↑

Use both clauses when you need to filter source rows and then filter the groups created from those rows.

For example, first consider only documents priced at 50 or more, then keep groups containing at least two of those records:

SELECT category,
       type,
       COUNT(*) AS total
FROM documents
WHERE price >= 50
GROUP BY category, type
HAVING COUNT(*) >= 2
ORDER BY category, type;

The WHERE condition removes low-price rows before grouping. HAVING then checks the size of each remaining group.

HAVING with Aggregate Aliases Top ↑

MySQL allows a SELECT alias to be referenced in HAVING:

SELECT category,
       type,
       COUNT(*) AS total
FROM documents
GROUP BY category, type
HAVING total < 3;

The equivalent aggregate expression is:

HAVING COUNT(*) < 3

HAVING with AVG() Top ↑

Return category/type groups whose average price is above 80:

SELECT category,
       type,
       AVG(price) AS average_price
FROM documents
GROUP BY category, type
HAVING AVG(price) > 80
ORDER BY average_price DESC;

See SQL AVG() for average calculations.

HAVING with SUM() Top ↑

Return categories whose total stock is greater than 50:

SELECT category,
       SUM(stock) AS total_stock
FROM documents
GROUP BY category
HAVING SUM(stock) > 50;

See SQL SUM().

HAVING with MIN() and MAX() Top ↑

Return categories where the highest price is at least 150:

SELECT category,
       MAX(price) AS highest_price
FROM documents
GROUP BY category
HAVING MAX(price) >= 150;

Or keep groups whose minimum stock is below 6:

SELECT category,
       type,
       MIN(stock) AS minimum_stock
FROM documents
GROUP BY category, type
HAVING MIN(stock) < 6;

Related tutorials: MAX() and MIN().

Multiple Conditions in HAVING Top ↑

Aggregate conditions can be combined with AND or OR.

SELECT category,
       type,
       COUNT(*) AS total,
       AVG(price) AS average_price
FROM documents
GROUP BY category, type
HAVING COUNT(*) >= 2
   AND AVG(price) > 70;

Both aggregate conditions must be true for the group to remain.

Find Duplicate Values with HAVING Top ↑

A common use of HAVING is identifying values that appear more than once:

SELECT name,
       COUNT(*) AS duplicate_count
FROM student2_1
GROUP BY name
HAVING COUNT(*) > 1;

GROUP BY forms one group per name, and HAVING removes groups that occur only once.

HAVING without GROUP BY in MySQL Top ↑

HAVING is most commonly used with GROUP BY, but MySQL also permits HAVING in some queries without an explicit GROUP BY.

For example, the complete filtered result can act as one aggregate group:

SELECT COUNT(*) AS total_documents
FROM documents
HAVING COUNT(*) > 10;

If the aggregate condition is true, the aggregate row is returned; otherwise no row is returned.

HAVING with PHP PDO Top ↑

When the HAVING threshold comes from application input, bind it as a prepared-statement value.

<?php
require 'config.php';

$minimum_count=2;

$stmt=$dbo->prepare(
    "SELECT category,
            type,
            COUNT(*) AS total
     FROM documents
     GROUP BY category, type
     HAVING COUNT(*) >= :minimum_count
     ORDER BY category, type"
);

$stmt->bindValue(
    ':minimum_count',
    $minimum_count,
    PDO::PARAM_INT
);

$stmt->execute();

while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    $category=htmlspecialchars(
        (string)$row['category'],
        ENT_QUOTES,
        'Windows-1252'
    );

    $type=htmlspecialchars(
        (string)$row['type'],
        ENT_QUOTES,
        'Windows-1252'
    );

    $total=(int)$row['total'];

    echo "$category - $type : $total<br>";
}

The HAVING threshold is data, so it can be bound safely. SQL identifiers such as column names should remain application-controlled.

HAVING Performance Notes Top ↑

• Use WHERE for row-level conditions whenever possible so unwanted rows can be removed before grouping.
• Use HAVING for conditions that genuinely depend on aggregate/group results.
• Indexes can help the WHERE, JOIN and grouping parts of a query, depending on the execution plan.
• Large GROUP BY operations can require temporary work or sorting before HAVING is applied.
• Use EXPLAIN when a grouped production query is unexpectedly slow.

Common SQL HAVING Mistakes Top ↑

Using WHERE with an aggregate expression Top ↑

Aggregate results such as COUNT(), SUM() and AVG() are calculated after WHERE. Use HAVING for conditions on those results.

Using HAVING for every filter Top ↑

If a condition applies to individual source rows, WHERE is normally the correct place and can reduce the amount of data that must be grouped.

Putting WHERE after GROUP BY Top ↑

The SQL clause order is WHERE before GROUP BY, then HAVING after GROUP BY.

Confusing an alias with a database column Top ↑

Aliases such as total are names created for the query result. MySQL can use SELECT aliases in HAVING, but portability differs across database systems.

Forgetting GROUP BY when per-group results are required Top ↑

Without GROUP BY, an aggregate query normally treats all matching rows as one group.

Assuming HAVING changes individual rows inside a group Top ↑

HAVING keeps or removes whole grouped result rows. Use WHERE when you need to decide which source rows participate in the grouping.

Related SQL Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: