PSQL: How to use HAVING to Query Aggregate Functions and Groups

Purple Succulent Blossom, Calandrinia Spectabillis

Let's say we have two tables, users and accounts. A user can have many accounts. These accounts have a column that represents the amount of money in that account. How do you find all the users with no money in their accounts? Or what about at least $500 in their accounts?

We can do this with ease using the HAVING clause. It will allow us to sum all the accounts belonging to a user and filter them by that sum.

Tables

Users

id - primary key

Accounts

id - primary key
user_id - Users.id, foreign key, the user the account belongs to
amount - the money in the account
type - the type of the account e.g. checking, saving, investment

Query

The query groups accounts by user_id and sums their amount fields. Then we use the HAVING clause to filter for any users with no money in all of their accounts.

SELECT user_id, SUM(amount) as total_amount
FROM accounts
GROUP BY user_id
HAVING SUM(amount) = 0;

The result looks like this:

user_id | total_amount
----------------------
12345   | 0
47890   | 0

If we want to find users with at least $500 in their accounts, we can use the same query, just replace the having clause with: HAVING SUM(amount) >= 500.

Using WHERE with HAVING

What if we wanted a list of users who had no money in their checking account? A user can have multiple checking accounts so we need to aggregate the amounts as well.

SELECT user_id, SUM(amount) as total_amount
FROM accounts
WHERE type = 'checking'
GROUP BY user_id
HAVING SUM(amount) = 0;

This finds all the checking accounts that belong to a user, sums their amounts, and selects the sums that are equal to zero. Read the caveats section if you want to find out why you can’t use type = 'checking' in the HAVING clause.

Caveats

• The HAVING clause has to be after a GROUP BY, it will not work without one. HAVING conditions only work on groups. If there is no GROUP BY there are no groups. PSQL treats the entire table returned by the SELECT as one group if there is no GROUP BY. It doesn’t make sense to filter groups if you only have one group.
• HAVING must appear before ORDER BY.
• You can use multiple HAVING clauses, just like you can use multipleGROUP BY clauses.
• The columns that can be used in the HAVING clause are limited to the results of aggregate functions (SUM, MIN, MAX, etc.) and the columns within the GROUP BY. You can’t use any other columns. That’s why we couldn’t use the HAVING clause to select the checking accounts.

That’s all folks!

Try It

You can try the HAVING clause here.

drop table IF EXISTS users, accounts;
create table users (id integer);
insert into users (id) values (1);
insert into users (id) values (2);
insert into users (id) values (3);
create table accounts (id integer, user_id integer, amount integer, name varchar);
insert into accounts (id, user_id, amount, name) values (1, 1, 0, 'checking');
insert into accounts (id, user_id, amount, name) values (2, 1, 0, 'saving');
insert into accounts (id, user_id, amount, name) values (3, 1, 0, 'mm');
insert into accounts (id, user_id, amount, name) values (1, 2, 100, 'checking');
insert into accounts (id, user_id, amount, name) values (2, 2, 10, 'saving');
insert into accounts (id, user_id, amount, name) values (3, 2, 5000, 'mm');
select user_id, SUM(amount) as total_amount
from accounts
group by user_id
having SUM(amount) = 0;

Sources

Finding documentation on the HAVING clause was difficult. There was nothing in-depth in the PSQL docs so I resorted to using the SQL docs but even those were sparse. If anyone finds better documentation, leave a comment.

7.2. Table Expressions

SQL - Having Clause

SQL Language Reference

SQL Language Reference