Q

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?


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close