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?
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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in March 2004