difficult GROUP BY - 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)

difficult GROUP BY

Author

Topic

manderson
Starting Member

6 Posts

Posted - 2006-10-25 : 20:45:06

I have a table with sports scores. Essentially, there are 4 columns: Team_1 (int), Team_2 (int), Team_1_Score (int), Team_2_Score (int).

I want to display the teams in order of their winning percentage.

Can someone help me? Thanks in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-25 : 21:00:22

SELECT Winner, Count(*) Wins FROM
(SELECT CASE WHEN Team_1_Score>Team_2_Score THEN Team_1 ELSE Team_2 END Winner FROM myTable WHERE Team_1_Score<>Team_2_Score) A
GROUP BY Winner
ORDER BY Wins DESC

manderson
Starting Member

6 Posts

Posted - 2006-10-25 : 21:22:29

That works great.

Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-25 : 21:58:55

If that's not a homework question, I don't know what is.

Now do the NFL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

manderson
Starting Member

6 Posts

Posted - 2006-10-26 : 00:49:08

quote:
Originally posted by X002548

If that's not a homework question, I don't know what is.

Now do the NFL

I'm using this code to make an NFL site.

I have one more question:

The SQL code lists, for example, a 4-3 team ahead of a 4-2 team. How can I adjust the code so that the losses are listed in an ascending order?

Thanks in advance.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-27 : 01:48:40

I'm not super proud of this as it uses temp tables, but it works. Sometimes the attempt to do everything set-based in just one select hurts my procedural head too much.

This uses Rob's query to find winners and a similar query for losers to populate two temp tables, and then joins them in the final query that produces teamid, wins, losses, and (wins-losses). ordered by the last column.


insert into scores 
select 6,2,3,4 union all
select 6,1,3,4 union all
select 6,1,3,4 union all
select 6,1,3,4 union all
select 7,2,5,4 union all
select 1,2,3,3 union all
select 2,3,4,3 union all
select 1,2,4,3 union all
select 1,3,4,3 union all
select 1,3,4,3 union all
select 1,3,3,4 union all
select 1,3,3,4 union all
select 1,3,3,4

declare @wins table (winner int, wins int)
declare @losses table (loser int, losses int)

insert into @wins
SELECT Winner, Count(*) Wins FROM
(SELECT 
	CASE WHEN Team_1_Score>Team_2_Score THEN Team_1 
		 ELSE Team_2 END as Winner FROM scores 
 WHERE Team_1_Score<>Team_2_Score) A
GROUP BY Winner

insert into @losses
SELECT Loser, Count(*) Losses FROM
(SELECT 
	CASE WHEN Team_1_Score<Team_2_Score THEN Team_1 
		 ELSE Team_2 END as Loser FROM scores 
 WHERE Team_1_Score<>Team_2_Score) A
GROUP BY Loser

select 
	w.winner as team
	,w.wins as wins
	,coalesce(l.losses,0) as losses
	,w.wins-coalesce(l.losses,0) as diff 
from 
	@wins w
left join 
	@losses l on l.loser=w.winner
union all
select 
	l.loser as team
	,0
	,l.losses
	,-l.losses
from 
	@losses l 
left join  
	@wins w on l.loser=w.winner
where w.winner is null
order by diff desc

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources