Calculating totals... Help! - SQL Server Forums

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... Help!

Author

Topic

Ic0n
Starting Member

18 Posts

Posted - 2003-04-03 : 13:35:34

Given the following data:

WarID Opponenet ScoreA ScoreB
------ ---------- ------- ------
1 | Clan A | 100 | 50
1 | Clan A | 100 | 10
2 | Clan B | 101 | 180
3 | Clan C | 146 | 21
3 | Clan C | 125 | 14
3 | Clan C | 136 | 52

How can i get a query to return :

Played Won Lost
------ ---- -----
3 | 2 | 1

I've been trying to work this out all day and still not managed it so any help would be greatly appreciated.

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-03 : 13:57:00

You definiteily need to provide information. I can only guess how you are getting to the results you mention with what you have told us. What does "played?" mean? Who has a win, who has a loss? why are there 6 sets of scores, but only 3 wins and losses combined? What if you had:

1 ClanA 100 50
1 ClanA 50 100

Would that be a win or a loss? or both? can this happen? what about ties?

anyway, my best guess:

select count(*) as played,
sum(case when TotalScoreA > TotalScoreB then 1 else 0 end) as Won,
sum(case when TotalScoreA < TotalScoreB then 1 else 0 end) as Lost
from
(
select warID, sum(scoreA) as TotalScoreA, sum(ScoreB) as TotalScoreB
from yourdata
group by warID
)
a

- Jeff

Ic0n
Starting Member

18 Posts

Posted - 2003-04-03 : 14:07:55

Ok, a match can be played over any number of maps ie. a final would be best of 5, a semi-final would only be best of 3.

Played means how many matches(not maps) a team has played so if they played in the semi and the final they would have played 2 matches

I didnt think of ties, so i guess i would need to allow for them.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-03 : 14:16:35

This should be pretty close to what you need:

select count(*) as played,
sum(case when WonA > WonB then 1 else 0 end) as Won,
sum(case when WonA < WonB then 1 else 0 end) as Lost
from
(
select warID,
sum(case when scoreA > scoreB then 1 else 0 end) as WonA,
sum(case when scoreA < ScoreB then 1 else 0 end) as WonB
from yourdata
group by warID
)
a

How's that? pretty close? it should at least give you some ideas. Break out the subquery and run it by itself to see how it works, and then see how the main query summarizes the subquery to return the results.

- Jeff

Ic0n
Starting Member

18 Posts

Posted - 2003-04-03 : 14:20:41

Thanks m8, that works perfectly :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources