SQL query with count() from another table

Question

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.

John Woo · Accepted Answer · 2013-11-09 19:13:49Z

2

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

answered Nov 9, 2013 at 19:13

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

1 Comment

kfirba Over a year ago

thanks! well explained, defenitely solved a problem issue with that LEFT JOIN since I haven't thought about the situation when no one has voted to some picture

M Khalid Junaid · Accepted Answer · 2013-11-09 19:14:05Z

1

Use left join with a group function ,normal join keyword means an inner join and you need a left join for one to many relation

SELECT `pictures`.`id`,`pictures`.`views`, COUNT(`votes`.`pic_id`) as votes
FROM `pictures` 
LEFT JOIN `votes`
ON `pictures`.`id` = `votes`.`pic_id`
GROUP BY `pictures`.`id`

answered Nov 9, 2013 at 19:14

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Comments

lukpaw · Accepted Answer · 2013-11-09 19:14:38Z

1

Try this:

SELECT `pictures`.`id`,`pictures`.`views`, COUNT(`votes`.`pic_id`) as votes
FROM `pictures` 
JOIN `votes`
ON `pictures`.`id` = `votes`.`pic_id`
GROUP BY `votes`.`pic_id`;

answered Nov 9, 2013 at 19:14

lukpaw

1,6132 gold badges26 silver badges31 bronze badges

Comments

Rahul Tripathi · Accepted Answer · 2013-11-09 19:14:48Z

1

You need to try like this using the GROUP BY clause:

SELECT `pictures`.`id`,`pictures`.`views`, COUNT(`votes`.`pic_id`) as votes
FROM `pictures` 
JOIN `votes`
ON `pictures`.`id` = `votes`.`pic_id`
group by `pictures`.`id`

answered Nov 9, 2013 at 19:14

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Collectives™ on Stack Overflow

SQL query with count() from another table

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related