CODEWITHSUNDEEP
sqlsql-server
1950
1950

Reputation: 191

How to count rows with specific value?

I got this SELECT, it shows result of match, but it count Goals even if Goal = 0. How can I define to count only where goals is equal to 1?

SELECT 
    B1.Name AS HomeTeam, ISNULL(C1.Goal, 0) AS HomeTeamScore, 
    ISNULL(C2.Goal, 0) AS AwayTeamScore, B2.Name AS AwayTeam
FROM 
    Match A
INNER JOIN 
    Team AS B1 ON A.HomeTeamId = B1.TeamId
INNER JOIN 
    Team AS B2 ON A.AwayTeamId = B2.TeamId
LEFT JOIN 
    (SELECT MatchId, TeamId, COUNT(Goal) AS Goal
     FROM PlayerMatch
     INNER JOIN Players ON Players.PlayerId = PlayerMatch.PlayerId
     GROUP BY MatchId, TeamId) C1 ON A.MatchId = C1.MatchId AND A.HomeTeamId = C1.TeamId
LEFT JOIN 
    (SELECT MatchId, TeamId, COUNT(Goal) AS Goal
     FROM PlayerMatch 
     INNER JOIN Players ON Players.PlayerId = PlayerMatch.PlayerId
     GROUP BY MatchId, TeamId) C2 ON A.MatchId = C2.MatchId AND A.AwayTeamId = C2.TeamId

Upvotes: 1

Views: 80

Answers (4)

LunarSage
LunarSage

Reputation: 285

Don't forget that COUNT() counts all non-null values, not just values > 0.

As a result, use:

COUNT(CASE WHEN goal > 0 THEN 1 ELSE NULL END)

or shortened to:

COUNT(CASE WHEN GOAL > 0 THEN 1 END)

Upvotes: 0

krishn Patel
krishn Patel

Reputation: 2599

simple if you have value 0 or 1 in Goal

sum(Goal) AS Goal

OR

 COUNT(case when Goal <> 0 then 1 end) AS Goal

Upvotes: 1

Rajan
Rajan

Reputation: 303

Use this.

LEFT JOIN (SELECT MatchId, TeamId, COUNT(Goal) AS Goal
        FROM PlayerMatch
        INNER JOIN Players ON Players.PlayerId = PlayerMatch.PlayerId
        GROUP BY MatchId, TeamId) C1 ON A.MatchId = C1.MatchId AND A.HomeTeamId =C1.TeamId 
        Having COUNT(Goal) = 1

Upvotes: 2

Rahul
Rahul

Reputation: 77866

You can include a WHERE condition saying where goal <> 0 or use conditional count() like

COUNT(case when Goal <> 0 then 1 else 0 end) AS Goal

Make your subquery like

 LEFT JOIN (SELECT MatchId, TeamId, COUNT(Goal) AS Goal
            FROM PlayerMatch
            INNER JOIN Players ON Players.PlayerId = PlayerMatch.PlayerId
            WHERE Goal <> 0 ---Here
            GROUP BY MatchId, TeamId) C1

Upvotes: 2

Related Questions