Multiple aggregate functions in HAVING clause

sql

prompt-engineering

best-practices

conditional-aggregates

byNikita Barsukov·Jan 19, 2025

Filter groups with multiple conditions on aggregate functions, combine HAVING, AND, and OR. Example: groups where SUM(column2) is over 100 and AVG(column3) is under 20:

-- Sir Table_Alots_table is ready for its aggregating party
SELECT
    column1,
    SUM(column2) AS total,  -- Add sums to the SUM-mons list!
    AVG(column3) AS average  -- Shall we say this operation is quite average?
FROM
    table_name
GROUP BY
    column1
HAVING
    SUM(column2) > 100   -- Gatekeeper's first rule: Need a sum more than 100 to enter
AND
    AVG(column3) < 20;   -- Gatekeeper's second rule: Average must be less than 20

Here, we are using SUM(column2) and AVG(column3) as our aggregate functions in the HAVING clause to filter results.

A deep dive into HAVING

The HAVING clause, filters results after the GROUP BY clause has done its job. The GROUP BY sorts your data into neat, unique groups, but HAVING allows you to go even deeper.

Count on COUNT

Filtering based on COUNT is a common usage of HAVING clause. An exact number (say, 3) could be achieved with:

HAVING COUNT(column) = 3

If you want a range, for instance counts between 3 and 6:

HAVING COUNT(column) > 2 AND COUNT(column) < 7

Sort your sums

Beyond counting, you could total a specific column with SUM(column) > amount. For example,

HAVING SUM(column) > 1000

Filters groups where the total exceeds a grand.

Similarly, to target groups with average values in a certain range, use BETWEEN.

HAVING AVG(column) BETWEEN 10 AND 20

Advanced Aggregates

Sometimes, simple isn't enough. In such cases, advanced solutions involving HAVING with complex aggregate functions come to rescue.

The Magic of COUNT(*)

Consider a case where you want to filter by the size of each group.

SELECT group_field, COUNT(*)
FROM your_table
GROUP BY group_field
HAVING COUNT(*) = 5  -- The magic number!

This will pinpoint groups with exactly five records.

SUM with Financial Transactions

Especially in financial tables, results boiled down to HAVING SUM() > value could be crucial. Consider this:

SELECT account, SUM(amount)
FROM transactions
GROUP BY account
HAVING SUM(amount) > 10000  -- In search of the big fish!

You're now only showing accounts having transactions that total more than $10,000.

ORDER BY for Sort-based Elegance

Ranking and sorting is a breeze with ORDER BY:

... HAVING SUM(amount) > 10000
ORDER BY SUM(amount) DESC  -- Top-down view gives the best view! 😉

This would rank accounts from highest to lowest total transaction.

Building towards mastery

Null handling

Bear in mind null values when using COUNT(column). To include nulls, use COUNT(*) or COALESCE to assign default values to nulls.

Conditional aggregates for complex scenarios

In cases where you want aggregate functions to be applied conditionally --

HAVING SUM(CASE WHEN condition THEN column ELSE 0 END) > threshold  -- The awaited condition~

Conditional aggregates prove to be a powerful tool for selectively summing values.

Grouping rules

Ensure to include all non-aggregate fields in your GROUP BY clause. Neglecting can lead to undefined behavior and incorrect results.

explain-codes / Sql / Multiple aggregate functions in HAVING clause

Linked

Filter by COUNT(*)?



Select where count of one field is greater than one



Mysql count group by having



Sql COUNT* GROUP BY bigger than



How to include BIT type column in SELECT part without including it on the GROUP BY in T-SQL?



Sql Group By with an Order By



What is the difference between HAVING and WHERE in SQL?



A deep dive into HAVING Advanced Aggregates Building towards mastery