Help with Query

• hprider

  SSC Enthusiast

  Points: 139

  More actions

  July 14, 2008 at 7:37 pm

  #184617

  Hi All,

  I can't seem to figure out what should be a fairly easy query. Any help would be appreciated. 🙂

  The query should bring back a list of bowling center ids, the number of active bowling leagues with team members who are also members of the site (MemberID > 0), and the total number of members per bowling center.

  As it stands it looks like I'm getting the correct total of users per bowling center but I can't figure out how to get the correct number of distinct leagues. The query currently returns the same count for leagues as it does for members.

  select distinct top (5) bc.BowlingCenterID, count(le.LeagueID) as LeagueCount,count(tm.MemberID) as count

  From mb_BowlingCenter bc, mb_League le, mb_Team t, mb_TeamMember tm

  where le.IsActive = 1

  And le.LeagueID = t.LeagueID

  And t.TeamID = tm.TeamID

  And tm.MemberID > 0

  and le.BowlingCenterID = bc.BowlingCenterID

  GROUP BY bc.BowlingCenterID

  order by count desc

  Thanks!

  Howard

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  July 14, 2008 at 8:31 pm

  #842774

  Try moving the DISTINCT keyword:

  select bc.BowlingCenterID

  , count(DISTINCT le.LeagueID) as LeagueCount

  , count(tm.MemberID) as count

  FROM ....

  SQL = Scarcely Qualifies as a Language

• hprider

  SSC Enthusiast

  Points: 139

  More actions

  July 14, 2008 at 8:51 pm

  #842776

  Hi Carl,

  That worked perfectly. I didn't realize I could use the distinct keyword in that location.

  Thanks!

  Howard