Count() with multiple conditions in SQL

Question

I'm trying to see how many times a player has lost a match at any of his favourite stadiums. I've tried the following, but it is not returning the correct values:

select players.name,
count (case when players.team <> matches.winner and favstadiums.stadium = matches.stadium then 1 else null end) as LOSSES
from players
join favstadiums
on favstadiums.player = players.name 
join matches
on favstadiums.stadium = matches.stadium
group by players.name;

I've also tried left/right joins, but it makes no difference in the output.

Here is the relational diagram of the database for reference:

enter image description here

Any ideas?

You should use a numeric ID for each table (player.id, team.id, etc...) — Todd Moses
– Todd Moses, Commented May 8, 2013 at 19:30
@ToddMoses if you do that, at least make them meaningful: player.playerid, team.teamid. Otherwise your query will be full of a bunch of "id" this and "id" that, and make little sense. — Dave Johnson
– Dave Johnson, Commented May 8, 2013 at 20:18

Gordon Linoff · Accepted Answer · 2013-05-08 19:29:46Z

2

Your join condition doesn't have the player playing in the stadium. You need to add the condition that the player's team played in the favorite stadium:

select players.name,
       SUM(case when players.team <> matches.winner then 1 else 0 end) as Losses
from players join
     favstadiums
     on favstadiums.player = players.name join
     matches
     on favstadiums.stadium = matches.stadium and
        players.team in (matches.home, matches.away)
group by players.name;

answered May 8, 2013 at 19:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Martin Over a year ago

Thanks. I had a feeling this was the reason, but didn't know how to add it. Also do you know how I can get rid of rows with 0 losses. I tried adding and Losses is not null before the group by, but it doesn't work

Lamak · Accepted Answer · 2013-05-08 19:30:22Z

1

Try the following:

SELECT  P.name,
        COUNT(DISTINCT M.ID) AS Losses
FROM Player P
INNER JOIN favStadiums FS
    ON P.name = FS.player
INNER JOIN Match M
    ON (P.team = M.home OR P.team = M.away)
WHERE FS.stadium = M.stadium
AND M.winner <> P.team

answered May 8, 2013 at 19:30

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Comments

pranag · Accepted Answer · 2013-05-08 19:29:14Z

0

This should work, check it out

select player.name,
count(match.id) as LOSSES
from player
inner join team
    on player.team = team.name 
left join match
    inner join favStadiums on favstadiums.stadium = matches.stadium and favStadiums.player = player.name
    on (match.home = team.name or match.away = team.name) and match.winner <> team.name     
group by players.name;

answered May 8, 2013 at 19:29

pranag

5,6322 gold badges20 silver badges15 bronze badges

Comments

DRapp · Accepted Answer · 2013-05-08 19:34:43Z

0

Just another thrown into the mix... I indent my joins to show relation of how I get to the details.

SELECT
      P.`Name`,
      SUM( case when P.team <> M.winner then 1 else 0 end ) as FavStadiumLosses
   from
      Player P
         Join Match M
            ON P.Team = M.`Home`
            OR P.Team = M.Away
            Join Stadium S
               ON M.Stadium = S.Stadium
               JOIN FavStadiums FS
                  ON P.`Name` = FS.Player
                  AND M.Stadium = FS.Stadium
   group by
      P.`Name`

answered May 8, 2013 at 19:34

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Collectives™ on Stack Overflow

Count() with multiple conditions in SQL

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related