CODEWITHSUNDEEP
sqlsql-server
1950
1950

Reputation: 191

Count where value is 1

I got this SELECT. And I want to count (or sum?) Yellow and Red card of players (Count Yellow cards only where YellowCard = 1, and Count Red cards only where RedCard = 1).. This SELECT does not work correctly. It count card even if there are 0 or NULL.. How can i correct it? 

SELECT Firstname, Lastname, COUNT(YellowCard) AS Yellow, COUNT(RedCard) AS Red, Team.Name 
FROM PlayerMatch 
Inner join Player On PlayerMatch.PlayerId = Player.PlayerId 
INNER JOIN Team ON Player.TeamId = Team.TeamId 
INNER JOIN Match ON PlayerMatch.MatchId = Match.MatchId 
WHERE(YellowCard = 1 OR RedCard = 1) 
GROUP BY Lastname, Firstname, Name 
ORDER BY Yellow DESC

Upvotes: 2

Views: 92

Answers (3)

Serg
Serg

Reputation: 22811

No, NULLs are not counted. If YellowCard, RedCard take only 0,1,NULL values:

SELECT Firstname, Lastname, COUNT(nullif(YellowCard,0)) AS Yellow, COUNT(nullif(RedCard,0)) AS Red, Team.Name 
FROM PlayerMatch 
Inner join Player On PlayerMatch.PlayerId = Player.PlayerId 
INNER JOIN Team ON Player.TeamId = Team.TeamId 
INNER JOIN Match ON PlayerMatch.MatchId = Match.MatchId 
WHERE(YellowCard = 1 OR RedCard = 1) 
GROUP BY Lastname, Firstname, Name 
ORDER BY Yellow DESC

Upvotes: 2

Kiloumap
Kiloumap

Reputation: 2243

You can't do that because in SQL that will start from group & where clause, so your alias doesn't exist yet.

you have to use SQL subquery as this tutorial : http://www.dofactory.com/sql/subquery

That would work if u try : SELECT * FROM PlayerMatch Inner join... WHERE YellowCard in (SELECT * FROM yourTable WHERE YellowCard = 1) AND RedCard in (SELECT * FROM yourTable WHERE RedCard = 1)...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Use SUM() instead of COUNT():

SELECT Firstname, Lastname,
       SUM(YellowCard) AS Yellow, SUM(RedCard) AS Red, t.Name 
FROM PlayerMatch pm Inner join
     Player p
     On pm.PlayerId = p.PlayerId INNER JOIN
     Team t
     ON p.TeamId = t.TeamId INNER JOIN
     Match m
     ON pm.MatchId = m.MatchId 
WHERE (YellowCard = 1 OR RedCard = 1) 
GROUP BY Lastname, Firstname, Name
ORDER BY Yellow DESC;

This assumes that the values of YellowCard and RedCard are always 0 or 1 (or NULL). That seems like a reasonable assumption.

If that is not the case, then use CASE:

SELECT Firstname, Lastname,
       SUM(CASE WHEN YellowCard = 1 THEN 1 ELSE 0 END) AS Yellow,
       SUM(CASE WHEN RedCard = 1 THEN 1 ELSE 0 END) AS Red,
       t.Name

Upvotes: 7

Related Questions