Get users with item count <= 1 in sql-CodePudding

We have these tables in PostgreSQL 12:

User -> id, name, email
items -> id, user_id, description

We want to run a query to find users that have 1 item or less.

I tried using a join statement and in the WHERE clause tried to put the count of users < 1 with this query

select * from "user" inner join item on "user".id = item.user_id where count(item.user_id) < 1;

but it failed and gave me this error.

ERROR: aggregate functions are not allowed in WHERE LINE 1: ...inner join item on "user".id = item.user_id where count(item...

so im thinking the query needs to be more techincal. Can anyone please help me with this? thanks

CodePudding user response：

You can do:

select u.*
from user u
left join (
  select user_id, count(*) as cnt from items group by user_id
) x on x.user_id = u.id
where x.cnt = 1 or x.cnt is null

CodePudding user response：

You don't technical need a JOIN for this. You can get all the necessary data from the item table with GROUP BY. The trick is you need to use HAVING instead of WHERE for aggregated data like COUNT()

SELECT user_id
FROM item
GROUP BY user_id
HAVING COUNT(id) > 1

But we can add a JOIN if you want to see more fields from the user table:

SELECT u.id, u.name, u.email
FROM item i
INNER JOIN "user" u on u.id = i.user_id
GROUP BY u.id, u.name, u.email
HAVING COUNT(i.id) > 1