Question 113832 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Get users with item count <= 1 in sql

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    You don’t technically 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search