SQL join one to many relationship - count number of votes per image?

Question

Okay, so I have 2 tables:

images                           votes
----------------------------     --------------------
image_id |  name | square_id     vote_id |  image_id
----------------------------     --------------------
1         someImg    14          1            45  
2         newImg     3           2            18     
3         blandImg   76          3            1 
...                              ...
n                                n

This is a one to many relationship. Each image can have multiple votes, but a vote can only be related to one image. I'm trying to produce a join query which will show the image id, and the number of votes it has under a specified condition (say, based on square_id). Thus the query result would look similar to this:

query_result
----------------------
image_id |  vote_count
----------------------
18          46
26          32
20          18
...
55          1

But the best I can do is this:

query_result
----------------------
image_id |  vote_id
----------------------
18          46
18          45
18          127
26          66
26          43
55          1

See the problem? Each image_id is listed multiple times for each vote_id it has. This is the query which produces this:

SELECT images.image_id, votes.vote_id 
  FROM votes JOIN images ON images.image_id=votes.image_id

I just can't seem to create a vote_count column which is the sum of all the votes that image has. Is there some way that I can use the count() function to do so, that I'm simply not aware of?

Answer 1

You need to GROUP BY images.image_id and use COUNT(votes.vote_id):

SELECT images.image_id, COUNT(votes.vote_id) AS cote_count
FROM votes 
JOIN images ON images.image_id=votes.image_id
GROUP BY images.image_id

Answer 2

You generally need to use GROUP BY when using aggregates like COUNT():

SELECT images.image_id, count(votes.vote_id) AS vote_count
  FROM votes 
  JOIN images ON images.image_id=votes.image_id
  GROUP BY images.image_id;

I'm not 100% clear on what you mean by

and the number of votes it has under a specified condition (say, based on square_id)"?

Your model seems to model square_id against image and can only be used as a where filter on images, not on a relationship between votes and images.