Counting only some rows in a GROUP BY

Counting only some rows in a GROUP BY

I have the following tables:

tblVenue
- Venue_NUM (Primary Key)
- VenueName

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

tblRatings
- 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

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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
inner
  join tblShow
    on tblVenue.Venue_NUM = tblShow.Venue_NUM
inner
  join tblRatings
    on tblShow.Show_NUM = tblRatings.Show_NUM
group
    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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top