ALTER PROCEDURE cup_getTabell
@groupID int
AS
Select
t.Namn,t.ID,
sum(case when (t.ID = g.homeTeam and g.HomeScore > g.awayScore)
or (t.id = g.awayTeam and g.homeScore < g.awayScore) and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end) +
sum(case when (t.ID = g.homeTeam and g.HomeScore < g.awayScore)
or (t.id = g.awayTeam and g.homeScore > g.awayScore) and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end) +
sum(case when ((t.ID = g.homeTeam and g.HomeScore = g.awayScore)
and g.gameEnded = 1 and c.playOff = 0 or (t.id = g.awayTeam and g.homeScore = g.awayScore)
and g.gameEnded = 1 and c.playOff = 0) then 1 else 0 end) as gamesPlayed,
sum(case when (t.ID = g.homeTeam and g.HomeScore > g.awayScore)
or (t.id = g.awayTeam and g.homeScore < g.awayScore) and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end) as wins,
sum(case when (t.ID = g.homeTeam and g.HomeScore < g.awayScore)
or (t.id = g.awayTeam and g.homeScore > g.awayScore) and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end) as losses,
sum(case when ((t.ID = g.homeTeam and g.HomeScore = g.awayScore)
and g.gameEnded = 1 and c.playOff = 0
or (t.id = g.awayTeam and g.homeScore = g.awayScore)
and g.gameEnded = 1 and c.playOff = 0) then 1 else 0 end) as ties,
(sum(case when (t.ID = g.homeTeam and g.HomeScore > g.awayScore)
or (t.id = g.awayTeam and g.homeScore < g.awayScore) and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end)*2) +
sum(case when (t.ID = g.homeTeam and g.HomeScore = g.awayScore)
and g.gameEnded = 1 and c.playOff = 0 or (t.id = g.awayTeam and g.homeScore = g.awayScore)
and g.gameEnded = 1 and c.playOff = 0 then 1 else 0 end) as points,
sum((case when t.ID = g.homeTeam and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end)*homeScore +
(case when t.ID = g.awayTeam and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end) * awayScore) as GoalsForward,
sum((case when t.ID = g.homeTeam and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end)*awayScore +
(case when t.ID = g.awayTeam and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end) * homeScore) as GoalsAgainst,
sum((case when t.ID = g.homeTeam and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end)*homeScore +
(case when t.ID = g.awayTeam and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end) * awayScore) -
sum((case when t.ID = g.homeTeam and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end)*awayScore +
(case when t.ID = g.awayTeam and g.gameEnded = 1
and c.playOff = 0 then 1 else 0 end) * homeScore) as goalDifference
from
cup_deltagande_lag as t
left join cup_games as g
on (t.ID = g.homeTeam or t.ID = g.awayTeam)
left join cup_grupper as c
on c.id = g.groupID
where
t.gruppID = @groupID and c.playoff = 0
group by
t.ID,t.Namn,c.playoff,t.gruppID
order by
points desc, goaldifference Desc, goalsforward desc, t.Namn Asc
Problemet är att den räknar med matcher som inte är slut g.gameended = 0.
lägger jag till where g.gameended = 1 så fungerar det men då visas inte lagen som inte spelat några matcher.
Finns det något sätt att lösa det här på eller måste jag ha med where g.gameended = 1?
Problemet är at tdu tar med c.playoff i where-satsen, vilket gör att LEFT-joinarna tvingas bli INNER-joinar. Använd gärna en derived table som bas, eftersom det blir enklare att få översikt
SELECT t.Namn,
t.ID,
t.gruppID,
s.*
FROM cup_deltagande_lag AS t,
(SELECT t.ID,
COUNT(CASE WHEN g.gameEnded = 1 AND c.playOff = 0 THEN 1 ELSE 0 END) AS gamesPlayed,
SUM(CASE WHEN g.gameEnded = 1 AND c.playOff = 0 AND t.ID = g.homeTeam AND g.HomeScore > g.awayScore THEN 1 ELSE 0 END) AS homeWins,
SUM(CASE WHEN g.gameEnded = 1 AND c.playOff = 0 AND t.ID = g.homeTeam AND g.HomeScore < g.awayScore THEN 1 ELSE 0 END) AS homeLosses,
SUM(CASE WHEN g.gameEnded = 1 AND c.playOff = 0 AND t.ID = g.awayTeam AND g.HomeScore < g.awayScore THEN 1 ELSE 0 END) AS awayWins,
SUM(CASE WHEN g.gameEnded = 1 AND c.playOff = 0 AND t.ID = g.awayTeam AND g.HomeScore > g.awayScore THEN 1 ELSE 0 END) AS awayLosses,
SUM(CASE WHEN g.gameEnded = 1 AND c.playOff = 0 AND (t.ID = g.awayTeam OR t.ID=g.homeTeam) AND g.HomeScore = g.awayScore THEN 1 ELSE 0 END) AS [ties],
SUM(CASE WHEN g.gameEnded = 1 AND c.playOff = 0 THEN CASE WHEN t.ID = g.homeTeam THEN g.HomeScore ELSE g.awayScore END ELSE 0 END) AS goalsForward,
SUM(CASE WHEN g.gameEnded = 1 AND c.playOff = 0 THEN CASE WHEN t.ID = g.awayTeam THEN g.HomeScore ELSE g.awayScore END ELSE 0 END) AS goalsAgainst
FROM cup_deltagande_lag t
LEFT JOIN cup_games AS g
ON ( (t.ID = g.homeTeam OR t.ID = g.awayTeam) )
LEFT JOIN cup_grupper AS c
ON c.id = g.groupID
GROUP BY t.ID
) AS s
WHERE t.gruppID = 1 AND t.ID=s.ID
