CODEWITHSUNDEEP
sql
Martin
Martin

Reputation: 1090

Count() with multiple conditions in SQL

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?

Upvotes: 1

Views: 2217

Answers (4)

DRapp
DRapp

Reputation: 48139

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`

Upvotes: 0

Lamak
Lamak

Reputation: 70638

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 2

pranag
pranag

Reputation: 5622

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;

Upvotes: 0

Related Questions