COUNTs of Different Criteria 1

[BeeBeeBoy]

Hello,
I have two tables as follows. Each 'User' has a set of related contest entries. The entries can be either a 'video' or an 'image' (MediaType). I want to list the number of videos a user has along with the number of images a user has.

To get just the number of images for instance I can do this:
[tt]SELECT Users.UserID , COUNT(Entries.EntryID) AS NumImages
FROM Users
LEFT JOIN Entries
ON Entries.EntryID=Users.UserID AND Entries.MediaType='Image'
GROUP BY Users.UserID[/tt]

So I would like to take this query and 'merge' it with one like it but with a count of videos as well.


Users:

UserID  UserName
------------------
 1       Bob
 2       Fred
 3       Joe

Entries:

EntryID  UserID  MediaType
-----------------------
 1       2       Video
 2       2       Image
 3       1       Image
 4       3       Video

Desired Result:

UserName  NumVideos  NumImages
-------------------------------
 Bob        0          1
 Fred       1          1
 Joe        1          0

I'm a beginner at SQL. I'm sure there is an elegant solution to this, but I'm not finding it. Many thanks to anyone who can help me with this!

 

[r937]

one method:

select Users.UserID 
     , ( select count(*)
           from Entries
          where EntryID = Users.UserID 
            and MediaType = 'Video' ) as NumVideos
     , ( select count(*)
           from Entries
          where EntryID = Users.UserID 
            and MediaType = 'Image' ) as NumImages
  from Users

another method:

select Users.UserID 
     , sum(case when MediaType = 'Video'
                then 1 else 0 end ) as NumVideos
     , sum(case when MediaType = 'Image'
                then 1 else 0 end ) as NumImages
  from Users
left outer
  join Entries
    on Entries.EntryID = Users.UserID

r937.com | rudy.ca

 

[BeeBeeBoy]

Thanks a bunch!

That's exactly what I needed -- and simple and elegant as I figured it would be.