How to count rows with specific value?

Question

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

What is the data type of goal? Is it an int or bit? Can it only have the values 1 and 0? Or does it sometimes contain 3, 4, etc? — David Rushton
– David Rushton, Commented Mar 29, 2017 at 11:44

Rahul · Accepted Answer · 2017-03-29 11:41:20Z

2

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

edited Mar 29, 2017 at 11:41

answered Mar 29, 2017 at 11:38

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

krishn Patel Over a year ago

0 also take as a count 1

krishn Patel Over a year ago

may be it will work COUNT(case when Goal <> 0 then 1 end) AS Goal

Rajan · Accepted Answer · 2017-03-29 11:43:16Z

2

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

answered Mar 29, 2017 at 11:43

Rajan

3032 silver badges12 bronze badges

Comments

krishn Patel · Accepted Answer · 2017-03-29 11:49:42Z

1

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

edited Mar 29, 2017 at 11:49

answered Mar 29, 2017 at 11:38

krishn Patel

2,5991 gold badge22 silver badges31 bronze badges

1 Comment

Gordon Linoff Over a year ago

I think the OP intends SUM().

LunarSage · Accepted Answer · 2017-04-03 08:55:18Z

0

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)

edited Apr 3, 2017 at 8:55

answered Mar 29, 2017 at 13:00

LunarSage

2851 silver badge7 bronze badges

Collectives™ on Stack Overflow

How to count rows with specific value?

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related