Summera tabell med spelarresultat

Vet inte om det går att lösa med en SQL-fråga, jag använder MSSQL 2005.

Jag har två tabeller
Tabell Laginfo som innehåller fälten ID, LagNamn, Lagledare.
Tabell Resultat som innehåller fälten LagID, Score1, Time1, Score2, Time2

Om det går önskar jag summera ett lags reultat efter följande regel

Det kan vara 3-4 spelare i varje lag. Det 4:e resultatet ska räknas bort.
Sorteringen ska vara:

Order By LagID, score1, time1, score2, time2

Lag || Namn || score1 || time1 || score2 || time2
1 || spelare4 || 0 || 14,7 || 0 || 10,1
1 || spelare3 || 0 || 12,6 || 0 || 10,9
1 || spelare1 || 0 || 12,4 || 2 || 10,8
1 || spelare2 || 3 || 10,7

Min fråga är nu... Hur gör man för att summera score1, time1, score2, time2 utan att täkna in det sämsta resultatet.

Resultat för Lag 1 ska bli
score1 0
time1 39,7
score2 2
time2 31,8

Helst vill jag presentera följande utseende
Lag 1 || Lagnamn || 0 || 39,7 || 2 || 31,8

spelare4 || 0 || 14,7 || 0 || 10,1
spelare3 || 0 || 12,6 || 0 || 10,9
spelare1 || 0 || 12,4 || 2 || 10,8
spelare2 || 3 || 10,7

Hur beräknar man det fjärde resultatet?
Det finns ju både score1, time1, score2 och time2.
Vilket är bäst / vilket är sämst?

Varför har spelare 2 ingen tid/poäng på score2/time2? Hur skall det hanteras? Om vi tänker oss att spelare x har en bättre score1/time1 än spelare y, men att spelare y ändå har både score1/time1/score2/time2. Vem vinner då?

Det bästa är att ha 0 i både score 1 och score2 och så lite tid som möjligt. Om en spelare har mer än 0 i score1 for han inte fortsätta tävla, därför kan score2 och time2 vara NULL.

ok, hur rankas score mot time?

Om vi exempelvis har två användare
spelare 1
score1 - 0
time1 - 50
score2 - 0
time2 - 100

spelare 2
score1 - 0
time1 - 40
score2 - 1
time2 - 10

Så spelare 2 är mycket snabbare i både time1 och time2, men score2 så fick han 1 poäng. Vem vinner?

Är det alltid totaltiden och totalpoängen som avgör sorteringen? Eller är time1 viktigare än time2?

Score rankas högre än time. Se min tabell nedan, spelare 4 vinner före spelare 3 osv. För att delta i andra rundan måste man ha 0 fel i Score1 (runda1)

spelare4 || 0 || 14,7 || 0 || 10,1
spelare3 || 0 || 12,6 || 0 || 10,9
spelare1 || 0 || 12,4 || 2 || 10,8
spelare2 || 3 || 10,7

Spelare 1 hamnar efter spelare 3 eftersom han har 2 fel i score2 medan spelare 3 har 0 fel...

Om samma värde i Score2 så vinner snabbaste tiden (time2)
Om samma värde både i Score2 & Time2 så avgör Time1 vem som kommer först
Skulle det va samma värde i alla hamnar man på samma placering

Alla 4 spelare tillhör samma lag där resultaten av deras fel och tider ska summeras förutom för den spelare med sämst resultat.

Kan detta fungera?

--summering av lag och dess poäng..

select 
	resultat.lagid, LagNamn, SUM(score1) as score1, SUM(time1) as time1, SUM(score2) as score2, SUM(time2) as time2 
from 
	(select *, rank() over (partition by lagid order by score1+coalesce(score2,100), time1+coalesce(time2,0) desc) as 'rank' from #resultat) as resultat  
	inner join laginfo on resultat.LagId = laginfo.LagId 
where 
	[rank] < 4 
group by 
	resultat.LagId, laginfo.LagNamn

--de tre bästa från varje lag...
select 
	* 
from 
	(select rank() over (partition by lagid order by score1+score2, time1+time2 desc) as 'rank', lagid, namn, score1, time1, score2, time2 from resultat where score2 is not null and time2 is not null) t 
where 
	[rank] < 4
order by 
	lagid, score1+score2 asc, time1+time2 desc

Hej & tack för besväret.

Redigerat
tog bort # framför resultat och ändrade
ON resultat.LagId = laginfo.LagId till ON resultat.LagId = laginfo.Id

SELECT
[INDENT]resultat.lagid, 
LagNamn, 
SUM(score1) AS score1, 
SUM(time1) AS time1, 
SUM(score2) AS score2, 
SUM(time2) AS time2[/INDENT]
FROM
(SELECT *, rank() OVER (partition BY lagid
ORDER BY
[INDENT]score1 + COALESCE (score2, 100),
time1 + COALESCE (time2, 0) DESC) AS 'rank'[/INDENT]
FROM #resultat) AS resultat 
INNER JOIN
laginfo ON resultat.LagId = laginfo.LagId
WHERE     [rank] < 4
GROUP BY resultat.LagId, laginfo.LagNamn
SELECT * FROM
(SELECT rank() OVER (partition BY lagid
ORDER BY score1 + score2, time1 + time2 DESC) AS 'rank', lagid, namn, score1, time1, score2, time2
FROM resultat WHERE score2 IS NOT NULL AND time2 IS NOT NULL) t
WHERE     [rank] < 4
ORDER BY lagid, score1 + score2 ASC, time1 + time2 DESC

Stort tack, helt otroligt att lyckas på första försöket...

Allt fungerar perfekt efter min beskrivning, tyvärr var den felaktig :(

Jag skulle vilja ha summeringen av de 3 bästa tiderna i time1 oavsett hur tiderna ser ut i time2. Nu summeras de 3 tiderna i time1 efter de 3 snabbaste tiderna i time2

Ledsen för besväret...

Redigerat
Den verkar räkna fel på time2 också... ett exempel nedan

spelare1
score1 = 0
time1 = 60,00
score2 = 4
time2 = 46,20

spelare2
score1 = 0
time1 = 53,90
score2 = 4
time2 = 29,10

spelare3
score1 = 0
time1 = 58,20
score2 = 0
time2 = 34,60

spelare4
score1 = 0
time1= 52,80
score2 = 0
time2 = 35,20

Här borde resultatet efter summeringen bli
score1 = 0
time1 = 164,9
score2 = 4
time2 = 98,9

Men det blir
score1 = 0
time1 = 171
score2 = 4
time2 = 116

en annan vy av resultaten där det fetstilta borde vara det som summeras
0 || 60,00 || 4 || 46,20
0 || 53,90 || 4 || 29,10
0 || 58,20 || 0 || 34,60
0 || 52,80 || 0 || 35,20

Ok, då kan du ju ändra så att man inte summerar time1+time2 utan istället sorterar time1 först och om de är lika så går den efter time2.

Prova detta.

SELECTresultat.lagid, 
LagNamn, 
SUM(score1) AS score1, 
SUM(time1) AS time1, 
SUM(score2) AS score2, 
SUM(time2) AS time2FROM
(SELECT *, rank() OVER (partition BY lagid
ORDER BY score1 + COALESCE (score2, 100),
time1 DESC, COALESCE (time2, 0) DESC) AS 'rank'FROM #resultat) AS resultat 
INNER JOIN
laginfo ON resultat.LagId = laginfo.LagId
WHERE     [rank] < 4
GROUP BY resultat.LagId, laginfo.LagNamn

SELECT * FROM
(SELECT rank() OVER (partition BY lagid
ORDER BY score1 + score2, time1 DESC, time2 DESC) AS 'rank', lagid, namn, score1, time1, score2, time2
FROM resultat WHERE score2 IS NOT NULL AND time2 IS NOT NULL) t
WHERE     [rank] < 4
ORDER BY lagid, score1 + score2 ASC, time1 DESC, time2 DESC

Får felmeddelande nu

Felaktig syntax nära nyckelordet 'AS'

verkar vara vid första Order By

Hittade felet men den summerar fortfarande fel... Koden jag använder är nu

SELECT     resultat.lagid, LagNamn, SUM(score1) AS score1, SUM(time1) AS time1, SUM(score2) AS score2, SUM(time2) AS time2
FROM         (SELECT     *, rank() OVER (partition BY lagid
                       ORDER BY score1 + COALESCE (score2, 100), time1 DESC, COALESCE (time2, 0) DESC) AS 'rank'
FROM         resultat) AS resultat INNER JOIN
laginfo ON resultat.LagId = laginfo.Id
WHERE     [rank] < 4
GROUP BY resultat.LagId, laginfo.LagNamn
                          SELECT     *
                           FROM         (SELECT     rank() OVER (partition BY lagid
                                                  ORDER BY score1 + score2, time1 DESC, time2 DESC) AS 'rank', lagid, namn, score1, time1, score2, time2
                           FROM         resultat
                           WHERE     score2 IS NOT NULL AND time2 IS NOT NULL) t
WHERE     [rank] < 4
ORDER BY lagid, score1 + score2 ASC, time1 DESC, time2 DESC

Ok, jag trodde högre tid var bättre med tanke på ditt första inlägg

spelare4 || 0 || 14,7 || 0 || 10,1
spelare3 || 0 || 12,6 || 0 || 10,9
spelare1 || 0 || 12,4 || 2 || 10,8
spelare2 || 3 || 10,7

Prova denna koden då?

select 
	resultat.lagid, LagNamn, sum(score1) as score1, SUM(time1) as time1, SUM(score2) as score2, SUM(time2) as time2 
from 
	(select *, rank() over (partition by lagid order by score1+coalesce(score2,100), time1, coalesce(time2,0)) as 'rank' from resultat) as resultat 
	inner join laginfo on resultat.LagId = laginfo.id 
where 
	[rank] < 4 
group by 
	resultat.LagId, laginfo.LagNamn

select 
	* 
from 
	(select rank() over (partition by lagid order by score1+score2, time1, time2) as 'rank', lagid, namn, score1, time1, score2, time2 from resultat where score2 is not null and time2 is not null) t 
where 
	[rank] < 4
order by 
	lagid, score1+score2, time1, time2

Nu är det nära... Av 13 lag räknar den rätt på 12! Det blir bara ett fel

Här summerar den de tre första i time1 när den borde summera 1,3,4 (inte andra raden)
Summeringen i score1, score2, time2 är rätt, det är endast time1 som är fel
0 58,50 0 34,70
0 59,00 4 46,60
0 57,90 4 40,60
0 57,30 8 38,50

felaktigt
0 175,4 8 121,9

rätt
0 173,7 8 121,9

ok.. ytterligare feltolkningar. Jag tolkade det som att score1+score2 gick före tiden?

I ditt exempel får ju fjärde raden 8 i score vilket borde göra att den rankas sist?

Om vi rankar om så att time1 är viktigare än score2 så kommer ju inte totalsummeringen på 121.9 i time att stämma, och dessutom kommer det bli 12 (8+4) i score2.

Hur tänker du kring det?

Ursäkta röran...

Om det går att lösa vore rätt uträkning denna.

# steg1
Summera score1 och time1 där de tre bästa resultaten är score1, time1 (om det finns 2 eller fler spelare med samma score1 ska time1 särskilja där snabbast tid är bättre)

#steg2
samma som ovan fast med score2, time2

ok. Betyder det att följande ranking är rätt?

lagid namn score1 time1 score2 time2
2 spelare 4 0 57,3 8 38,5
2 spelare 3 0 57,9 4 40,6
2 spelare 1 0 58,5 0 34,7
2 spelare 2 0 59 4 46,6 (denna räknas inte i totalen)

Spelare 4 vinner ju på score1 och time1. Men han förlorar ju totalt sett eftersom han har 8 i score2. Hur

Det är fel
Vem vinnaren är i slutändan spelar ingen roll. Jag är bara intresserad av de 3 bästa reultaten i grundomgången (score1 & time1). Dessa personer behöver inte vara samma i den andra rundan (score2 & time2) I ditt exempel ovan så stämmer resultaten för score1 & time1. Där ska inte spelare2´s resultat räknas in. I Omgång 2 borde spelare 3,1,2 räknas eftersom seplare 4 har 8 i score2 och därmed sämst i andra rundan...

Hoppas du förstår...

2 spelare 4 0 57,3 8 38,5
2 spelare 3 0 57,9 4 40,6
2 spelare 1 0 58,5 0 34,7
2 spelare 2 0 59 4 46,6 (denna räknas inte i totalen)

ok, detta kan såklart lösas för summeringen.
Men när man faktiskt visar raderna så måste ju de sorteras på något sätt. Jag pratar om fråga nummer två som visar flera rader med de 3 bästa spelarna. Hur skall det fungera?

Det bästa vore om de sorterades efter startnumret, fältet heter (T_pos) i tabellen Resultat.

ok, prova detta?

select 
	resultat.lagid, LagNamn, SUM(CASE WHEN rank1 BETWEEN 1 AND 3 THEN score1 ELSE 0 END) as score1, SUM(CASE WHEN rank1 BETWEEN 1 AND 3 THEN time1 ELSE 0 END) as time1, SUM(CASE WHEN rank2 BETWEEN 1 AND 3 THEN score2 ELSE 0 END) as score2, SUM(CASE WHEN rank2 BETWEEN 1 AND 3 THEN time2 ELSE 0 END) as time2 
from 
	(select *, 
		rank() over (partition by lagid order by score1, time1) as 'rank1', 
		rank() over (partition by lagid order by score2, time2) as 'rank2' from resultat) as resultat 
	inner join laginfo on resultat.LagId = laginfo.id 
where 
	rank1 < 4 or rank2 < 4
group by 
	resultat.LagId, laginfo.LagNamn

select 
	* 
from 
	(select rank() over (partition by lagid order by score1, time1, score2, time2) as 'rank', lagid, namn, score1, time1, score2, time2, t_pos from resultat where score2 is not null and time2 is not null) t 
where 
	[rank] < 4
order by 
	lagid, score1, time1, t_pos