SQL Count within a Select

Question

I would like to display a gallery of photos and how many times each photo has been rated.

Here's my simply query but not sure how to get the Count from the Ratings table.

SELECT
   Photo.photoID
  ,Photo.photoName
  ,Member.memberID
  ,Member.memberFName
  ,Rating.ratingID
FROM
  Photo
INNER JOIN Member ON Photo.memberID = Member.memberID
LEFT OUTER JOIN Rating ON Photo.photoID = Rating.photoID
ORDER BY Photo.photoID DESC

Every time someone clicks the Star Rating buttons, a new record is inserted into the Rating table. I am only allowing a photo to be rated 100 times, therefore I would like to display the number of times a photo has been rated (i.e. 57/100) when the user is browsing the gallery.

Appreciate your help!

Answer 1

You can use the count function with a group by statement to get the count of ratings.

select p.photoID,
p.photoName,
m.memberID,
m.memberFName,
count(r.RatingID) as RatingCount
from Photo p
inner join Member m on p.memberID = m.memberID
left outer join Rating r on p.photoID = r.photoID
group by p.photoID, p.photoName, m.memberID, m.memberFName

Answer 2

select p.photoID,
    p.photoName,
    m.memberID,
    m.memberFName,
    coalesce(rc.Count, 0) as RatingCount
from Photo p
inner join Member m on p.memberID = m.memberID
left outer join (
    select photoID, count(*) as Count
    from Rating
    group by photoID
) rc on p.photoID = rc.photoID
order by p.photoID desc