Ask the Expert

Counting only some rows in a GROUP BY

I have the following tables:

- Venue_NUM (Primary Key)
- VenueName

- Show_NUM (Primary Key)
- Venue_NUM (Link to tblVenue)
- BandName

- Ratings_NUM (Primary Key)
- Show_NUM (Link to tblShow)
- Rating
- IP_Address

I am trying to figure out how to write an SQL statement that would show the VenueName, the BandName, the count of ratings entered by the person at IP address nnn.mmm.1.1, and the average of all the ratings for that BandName. Is it possible in one SQL statement?

    Requires Free Membership to View

With a straight three-way inner join, we will retrieve all ratings for all shows at all venues. So we can do the overall average quite easily. But counting the ratings for a specific IP will require some filtering. Luckily, we can do this filtering right in the SELECT list with a CASE expression:

select VenueName
     , BandName
     , sum(case when IP_Address 
                   = 'nnn.mmm.1.1'
                then 1 else 0 
            end)   as IPcount
     . avg(Rating) as AvgRating
  from tblVenue
  join tblShow
    on tblVenue.Venue_NUM = tblShow.Venue_NUM
  join tblRatings
    on tblShow.Show_NUM = tblRatings.Show_NUM
    by VenueName
     , BandName 

Note that summing a bunch of 1's and 0's gives the desired count, because the 1's correspond only to those rows we're filtering for.

For More Information

This was first published in March 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: