Calculating totals and grouping.... Help Please!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculating totals and grouping.... Help Please!

Author

Topic

Ic0n
Starting Member

18 Posts

Posted - 2003-04-07 : 09:30:22

Given the following data:

ResultID MatchID ScoreA ScoreB
----------- ----------- ----------- -----------
1 | 1 | 100 | 50
2 | 1 | 100 | 10
3 | 2 | 70 | 80
4 | 2 | 80 | 70
5 | 3 | 220 | 52
6 | 4 | 58 | 65
7 | 4 | 84 | 102

Match 1 was played over 2 maps and TeamA won 2 - 0.
Match 2 was played over 2 maps and it was a draw 1 - 1.
Match 3 was played over 1 map and TeamA won 1 - 0.
Match 4 was played over 2 maps and TeamB won 2 - 0.

I need to display the data as follows:

MatchID ScoreA ScoreB
------- ------ ------
1 | 2 | 0
2 | 1 | 1
3 | 220 | 52
4 | 0 | 2

So if a match was played over multiple maps i want display maps won / maps lost ie. 2 - 0, if a match was only played over 1 map i want to display the scores ie. 220 - 52.

Is this possible with SQL?

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2003-04-07 : 09:47:14

Don't know if this is the quickest way, but it works.


Select Distinct a.MatchID, 
Case a.matches
	When 1 then b.ScoreA
	Else c.won
End ScoreA,
Case a.matches
	When 1 then b.ScoreB
	Else c.Lost
End ScoreB
FROM (
Select MatchID, Count(*) Matches
From matches
Group by MatchID) a
LEFT JOIN matches b
on a.MatchID = b.MatchID
LEFT JOIN
(Select aa.MatchID, sum(aa.Won) Won, sum(aa.Lost) Lost
FROM (
Select MatchID, 
Case 
	When ScoreA > ScoreB then 1 
	else 0
	End Won,
Case 
	When ScoreA < ScoreB then 1 
	else 0
	End Lost
From matches) aa
GROUP BY aa.MatchID) c
on a.MatchID = c.MatchID

Jeremy

Edited by - joldham on 04/07/2003 10:21:05

Ic0n
Starting Member

18 Posts

Posted - 2003-04-07 : 10:09:56

Thanks very much m8, works exactly how i need it to and it seems to work pretty quick :)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-07 : 10:26:58

Here's a shorter, more direct method:

select matchID,
case when results = 1 then ScoreA else WinA end as ScoreA,
case when results = 1 then ScoreB else WinB end as ScoreB
from
(
select matchID,
sum(ScoreA) as ScoreA,
sum(ScoreB) as ScoreB,
sum(case when scoreA > scoreB then 1 else 0 end) as WinA,
sum(case when scoreA < scoreB then 1 else 0 end) as WinB,
count(*) as Results
From
yourtable
group by MatchID
)
a

- Jeff

Edited by - jsmith8858 on 04/07/2003 10:27:34

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources