PostgreSQL: Joining the count of another table

Question

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?

peterm · Accepted Answer · 2014-01-04 08:25:12Z

11

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

edited Jan 4, 2014 at 8:25

answered Jan 4, 2014 at 8:06

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Aldridge Over a year ago

With a primary key on users.id, you can take name out of the group by in the first query. The second query ought to be the most efficient, of course.

user2058002 Over a year ago

peterm you wouldn't happen to be the guy on robot game would you?

peterm Over a year ago

@Brandon No I'm not the guy. But I'm not sure is it good or bad? ;)

Pierre · Accepted Answer · 2014-01-04 08:10:20Z

3

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;

answered Jan 4, 2014 at 8:10

Pierre

9,1704 gold badges73 silver badges88 bronze badges

2 Comments

David Aldridge Over a year ago

The group-by in the last line is not required.

David Aldridge Over a year ago

... and not as efficient a method as joining to pre-aggregated result sets, unless you only wanted a very small subset of users included.

Collectives™ on Stack Overflow

PostgreSQL: Joining the count of another table

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related