5

I have the the following SQL statement:

 SELECT        [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME, [lp.PositionId]
 FROM            (Leagues l INNER JOIN
                     Lineups lp ON l.LeagueId = lp.LeagueId)
 WHERE        (lp.PositionId = 1) OR
                     (lp.PositionId = 3) OR
                     (lp.PositionId = 2)

What I really need is to get the rows where the count of the position is greater than a number. Something like:

 SELECT        [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME, [lp.PositionId]
 FROM            (Leagues l INNER JOIN
                     Lineups lp ON l.LeagueId = lp.LeagueId)
 WHERE        Count(lp.PositionId = 1) > 2 OR
                     Count(lp.PositionId = 3) > 6 OR
                     Count(lp.PositionId = 2) > 3

Is there any way to do this in SQL?

Kris B
  • 3,436
  • 9
  • 64
  • 106
  • So, I'm using Cade Roux's solution below, but does anyone know why, when I change the HAVING from an OR statement to and AND statement, I get zero results? If I only use one SUM(CASE..) statement, I get results, but if I combine two statement that worked, I get zero results. – Kris B Aug 09 '10 at 15:14

2 Answers2

9

How about this?:

SELECT        [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME
 FROM            (Leagues l INNER JOIN
                     Lineups lp ON l.LeagueId = lp.LeagueId)
 GROUP BY [l.LeagueId], [l.LeagueName]
 HAVING        SUM(CASE WHEN lp.PositionId = 1 THEN 1 ELSE 0 END) > 2 OR
                     SUM(CASE WHEN lp.PositionId = 3 THEN 1 ELSE 0 END) > 6 OR
                     SUM(CASE WHEN lp.PositionId = 2 THEN 1 ELSE 0 END) > 3
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
7

HAVING is the keyword you're looking for:

SELECT        [l.LeagueId] AS LeagueId, [l.LeagueName] AS NAME, [lp.PositionId]
FROM            (Leagues l INNER JOIN
                     Lineups lp ON l.LeagueId = lp.LeagueId)
GROUP BY lp.PositionId, l.LeagueName
HAVING lp.PositionId = 1 AND COUNT(*) > 2 
       OR lp.PositionId = 2 AND COUNT(*) > 3 
       OR lp.PositionId = 3 AND COUNT(*) > 6
Scott Stafford
  • 43,764
  • 28
  • 129
  • 177