sql count() from multiple tables

Question

There are tables like these:

pictures:

wall_id|picture_id|user_id|likes
      1|         1|      1|    2
      1|         2|      1|    0
      2|         1|      1|    1
      2|         2|      2|    2

Pair (wall_id, picture_id) is unique

likers:

wall_id|picture_id|user_id
      1|         1|      3
      1|         1|      2
      2|         1|      2
      2|         2|      4
      2|         2|      3

I want to get something like that:

    user_id|pictures_count|likes_count|likers_count
          1|             3|          3|           2
          2|             1|          2|           2

I tried this:

select p.user_id as user_id, 
    count(p.user_id) as pictures_count, 
    sum(p.likes) as likes_count, 
    count(distinct l.user_id) as likers_count
 from pictures p
 left join likers l on p.wall_id = l.wall_id 
                       and p.picture_id = l.picture_id
 group by p.user_id

and

select pictures.user_id, count(pictures.user_id) as pictures_count,
     sum(pictures.likes) as likes_count, 
    count(distinct likers.user_id) as likers_count 
from pictures, likers 
where pictures.picture_id = likers.picture_id 
    and pictures.user_id = likers.user_id 
group by pictures.user_id

But I get such result:

    user_id|pictures_count|likes_count|likers_count
          1|             4|          6|           2
          2|             2|          4|           2

What should I do to get right result?

Gordon Linoff · Accepted Answer · 2015-02-26 11:11:17Z

Joins are curious things. When you have a key and multiple rows match on both sides, then you get more rows that you expect. The solution is to pre-aggregrate the rows on each side.

This is a bit complicated with your data model, because you need the join to look up the user id for the likes table.

 select p.user_id as user_id, p.pictures_count, p.likes_count, l.likers_count
 from (select p.user_id, count(*) as pictures_count, sum(likes) as likes_count
       from pictures p
       group by p.user_id
      ) p left join
      (select p.user_id, count(distinct l.user_id) as likers_count
       from pictures p left join
            likers l
            on p.wall_id = l.wall_id and p.picture_id = l.picture_id
       group by p.user_id
      ) l
      on p.user_id = l.user_id;

Notice that because the aggregations are done in subqueries, it is no longer needed in the outer query.

Raging Bull · Accepted Answer · 2015-02-26 11:21:21Z

0

Try this:

SELECT T1.user_id,T1.pictures_count,T1.likes_count,T2.likers_count FROM
(select p.user_id,
       count(*) AS pictures_count,
       SUM(p.likes) as likes_count
from pictures p 
group by p.user_id) T1 JOIN

(select p.user_id,count(distinct l.user_id) as likers_count
from pictures p join
     likers l on p.wall_id = l.wall_id and p.picture_id = l.picture_id
group by p.user_id) T2 on T1.user_id=T2.user_id

Result:

USER_ID PICTURES_COUNT  LIKES_COUNT LIKERS_COUNT
1       3               3           2
2       1               2           2

See result in SQL Fiddle.

answered Feb 26, 2015 at 11:21

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Comments

Andomar · Accepted Answer · 2015-02-26 12:23:05Z

0

A join can cause rows to be repeated. You could apply the aggregates before joining.

select  *
from    (
        select  wall_id
        ,       sum(likes) as likes_count
        ,       count(*) as picture_count
        from    pictures
        group by
                wall_id
        ) as pictures
left join
        (
        select  wall_id
        ,       count(distinct user_id) as likers_count
        from    likers
        group by
                wall_id
        ) as likes 
on      pictures.wall_id = likes.wall_id

edited Feb 26, 2015 at 12:23

answered Feb 26, 2015 at 11:09

Andomar

239k55 gold badges387 silver badges412 bronze badges

1 Comment

Raging Bull Over a year ago

Your query results this which is different from OP's expected output.

Collectives™ on Stack Overflow

sql count() from multiple tables

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related