jeudi 26 février 2015

sql count() from multiple tables

There are tables like these:




  1. 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




  1. 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? Thanks.


Aucun commentaire:

Enregistrer un commentaire