Q:

SQL query with count() from another table

I got the following tables:
pictures
------------------------------------
id
name
views

votes
------------------------------------
id
user_id
pic_id

I want to get a result from a query that will give me each picture id, with the views of the picture, and the total votes from the table votes for the specific pic_id
example:
pictures.id, pictures.views, total votes
1 ------------ 78------------------ 123
2 ------------ 23------------------- 69
and so on...
The code I tried:
SELECT `pictures`.`id`,`pictures`.`views`, COUNT(`votes`.`pic_id`) as votes
FROM `pictures` 
JOIN `votes`
ON `pictures`.`id` = `votes`.`pic_id`

But it doesn't give me the reuslt I desire.

A:

You need to have GROUP BY clause. 
The use of LEFT JOIN will display all records on table pictures with or without matching record on table votes.
SELECT  a.id, a.name, a.views,
        COUNT(b.pic_id) TotalVotes
FROM    pictures a
        LEFT JOIN votes b
            ON a.id = b.pic_id
GROUP   BY a.id, a.name, a.views

