CODEWITHSUNDEEP
sqlpostgresqljoinleft-join
user2058002
user2058002

Reputation:

PostgreSQL: Joining the count of another table

I have three tables: users, items, and boards.

create table users (
    id serial primary key,
    name text
);  

create table items (
    id serial primary key,
    user_id int,
    title text
);  

create table boards (
    id serial primary key,
    user_id int,
    title text
);n

I want to select all the users with the number of items and boards each user has:

select
    users.*,
    count(items) as item_count,
    count(boards) as board_count
from users
    left join items on items.user_id = users.id
    left join boards on boards.user_id = users.id
group by users.id;

Unfortunately, this does not give me the right counts. The counts are way higher than they should be. What is happening, why, and how can I fix this?

Upvotes: 8

Views: 4445

Answers (2)

peterm
peterm

Reputation: 92785

The problem is caused by fact that two left joins produce duplicate values.

To solve this you can either count only distinct values

SELECT u.id, u.name,
       COUNT(DISTINCT i.id) item_count,
       COUNT(DISTINCT b.id) board_count
  FROM users u LEFT JOIN items i
    ON u.id = i.user_id LEFT JOIN boards b
    ON u.id = b.user_id
 GROUP BY u.id, u.name

or count items and boards per user separately and then join them with users

SELECT u.*, 
       COALESCE(i.item_count, 0) item_count, 
       COALESCE(b.board_count, 0) board_count
  FROM users u LEFT JOIN
(
  SELECT user_id, COUNT(*) item_count
    FROM items
   GROUP BY user_id
) i ON u.id = i.user_id LEFT JOIN
(
  SELECT user_id, COUNT(*) board_count
    FROM boards
   GROUP BY user_id
) b ON u.id = b.user_id

Here is SQLFiddle demo

Upvotes: 11

Pierre
Pierre

Reputation: 9052

I think this should do the trick

select
    u.*,
    (select count(*) from items where user_id = u.id) as item_count,
    (select count(*) from boards where user_id = u.id) as board_count
from users as u
group by u.id;

Upvotes: 3

Related Questions