---
title: "Problem med hockeytabell"
type: "forum-thread"
url: "https://www.webforum.nu/amne/databaser-sql/135923-problem-med-hockeytabell"
topic: "Databaser & SQL"
topic_url: "https://www.webforum.nu/amne/databaser-sql"
author: "andreas_lindh"
published: "2005-10-18T08:22:31.000Z"
updated: "2005-10-19T07:48:27.000Z"
replies: 2
views: 250
page: 1
pages: 1
language: "sv-SE"
site: "webForum — webforum.nu"
rights: "Upphovsrätten till varje inlägg tillhör dess författare."
attribution: "Citera som: webForum, https://www.webforum.nu/amne/databaser-sql/135923-problem-med-hockeytabell"
---

# Problem med hockeytabell

## #1 — andreas_lindh, 2005-10-18T08:22Z

Jag har den frågan som räknar ut en hockeytabell

```
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?

Permalänk: https://www.webforum.nu/p/135923

## #2 — emission, 2005-10-18T22:55Z

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
```

Permalänk: https://www.webforum.nu/p/1689185

## #3 — andreas_lindh, 2005-10-19T07:48Z

Löste det. hade missat några parenteser

Permalänk: https://www.webforum.nu/p/1689235

---

Tråden på webben: https://www.webforum.nu/amne/databaser-sql/135923-problem-med-hockeytabell