Grouping on a CASE column - 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 2005 Forums

Transact-SQL (2005)

Grouping on a CASE column

Author

Topic

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-02-01 : 14:24:28

I want to have a query which shows soccer teams numbers of shutouts by season.
Something like this


SELECT  tblMatchTeam.TEAMID, Count(tblMatchTeam.TEAMID) AS ShutOutAcheived,

Season = 
case when (tblMatch.[Date] > CONVERT(DATETIME,  '1992-08-01' , 102) AND tblMatch.[Date] < CONVERT (DATETIME,  '1993-08-01' , 102))  Then '1992/3' 
 when (tblMatch.[Date] > CONVERT(DATETIME,  '1993-08-01' , 102) AND tblMatch.[Date] < CONVERT (DATETIME,  '1994-08-01' , 102))  Then '1993/4' 
end
     
     


FROM (tblMatch INNER JOIN tblMatchTeam AS tblMatchTeam_1 ON tblMatch.MATCHID = tblMatchTeam_1.MATCHID) INNER JOIN tblMatchTeam ON tblMatch.MATCHID = tblMatchTeam.MATCHID
WHERE (((tblMatchTeam_1.TEAMID)<>[tblMatchTeam].[TEAMID]) AND ((tblMatchTeam_1.GOALS)=0))
GROUP BY  tblMatchTeam.TEAMID, Season

but season is an invalid colum

In MS Access I have a function for the season which I can then use for grouping. Should I be looking for something similar in MSSQL or is there a simpler solution

cheers

Andrew Clark
www.premiersoccerstats.com

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 14:28:46

You cannot use a SELECT Alias name in a GROUP BY. You have to repeat the whole Expression instead - bit daft, I know, but that's how it is!


GROUP BY  tblMatchTeam.TEAMID,
case when (tblMatch.[Date] > CONVERT(DATETIME,  '1992-08-01' , 102) AND tblMatch.[Date] < CONVERT (DATETIME,  '1993-08-01' , 102))  Then '1992/3' 
 when (tblMatch.[Date] > CONVERT(DATETIME,  '1993-08-01' , 102) AND tblMatch.[Date] < CONVERT (DATETIME,  '1994-08-01' , 102))  Then '1993/4' 
end

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-01 : 14:32:28

There are several ways to do this, here is one:

SELECT 
	TeamID,
	COUNT(TeamID) AS ShutOutAcheived,
	Season
FROM
	(
		SELECT  
			tblMatchTeam.TEAMID, 
			--Count(tblMatchTeam.TEAMID) AS ShutOutAcheived,
			case 
				when (tblMatch.[Date] > CONVERT(DATETIME,  '1992-08-01' , 102) AND tblMatch.[Date] < CONVERT (DATETIME,  '1993-08-01' , 102))  
					Then '1992/3' 
				when (tblMatch.[Date] > CONVERT(DATETIME,  '1993-08-01' , 102) AND tblMatch.[Date] < CONVERT (DATETIME,  '1994-08-01' , 102))  
					Then '1993/4' 
			end AS Season
		FROM 
			tblMatch 
		INNER JOIN 
			tblMatchTeam AS tblMatchTeam_1 
			ON tblMatch.MATCHID = tblMatchTeam_1.MATCHID) 
		INNER JOIN 
			tblMatchTeam 
			ON tblMatch.MATCHID = tblMatchTeam.MATCHID
		WHERE 
			tblMatchTeam_1.TEAMID <> [tblMatchTeam].[TEAMID] 
			AND tblMatchTeam_1.GOALS = 0
	) AS D	
GROUP BY  
	TEAMID, 
	Season

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 14:37:07

Interesting academic debate.

Is it better to have the "computed" column in a nested select, and GROUP BY in the outer select (which requires all rows from Inner select to be "processed" by outer select), or to have the SELECT Computed Column expression "repeated" in the GROUP BY phrase?

Repeating it opens you up to making errors during maintenance ...

... wrapping the Query with an outer select might be slower, performance-wise - although I'm pretty sure that MLaden demonstrated that duplicating the SELECT expression in the GROUP BY caused it to be evaluated twice, rather than just being optimised-out

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-02-01 : 14:53:18

the other way is to use common table expressions CTE. Would this work?


;with cte(TEAMID, Season) AS 
(
SELECT  
			tblMatchTeam.TEAMID, 
			--Count(tblMatchTeam.TEAMID) AS ShutOutAcheived,
			case 
				when (tblMatch.[Date] > CONVERT(DATETIME,  '1992-08-01' , 102) AND tblMatch.[Date] < CONVERT (DATETIME,  '1993-08-01' , 102))  
					Then '1992/3' 
				when (tblMatch.[Date] > CONVERT(DATETIME,  '1993-08-01' , 102) AND tblMatch.[Date] < CONVERT (DATETIME,  '1994-08-01' , 102))  
					Then '1993/4' 
			end AS Season
		FROM 
			tblMatch 
		INNER JOIN 
			tblMatchTeam AS tblMatchTeam_1 
			ON tblMatch.MATCHID = tblMatchTeam_1.MATCHID) 
		INNER JOIN 
			tblMatchTeam 
			ON tblMatch.MATCHID = tblMatchTeam.MATCHID
		WHERE 
			tblMatchTeam_1.TEAMID <> [tblMatchTeam].[TEAMID] 
			AND tblMatchTeam_1.GOALS = 0
)

SELECT 
	TeamID,
	COUNT(TeamID) AS ShutOutAcheived,
	Season
FROM cte
GROUP BY  
	TEAMID, 
	Season

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-01 : 14:59:53

quote:
Originally posted by Kristen

Interesting academic debate.

Indeed!

Not to derail the actual acedemic part, but if you are getting down to this level of tuning in your stored procedures/code, then you are already light years ahead of most databases. :)

In this particular example, the date conversion and string comparisions alone would far out weigh any performance differences of "repeating" or "wrapping" the case statement. Or so I would think..

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-02-01 : 15:04:14

Thanks for the swift replies. This has to be the best Forum going
Kristen - your suggestion works Tx
Lamprey - Im getting the error
Column 'tblMatchTeam.TEAMID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. with your option

Re academic debate. Does it matter how many seasons there are? I have 2 above but actual code will be 18 and counting

Andrew Clark
www.premiersoccerstats.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-01 : 15:21:25

Can you post the actual query you ran?

Double check that you removed all the aggregate stuff from the derived table.

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-02-01 : 21:14:04

Lamprey/yosiasz

When I copy, paste and run each of yours as above I get the error

Incorrect syntax near the keyword 'INNER' (From line number, I think this is second INNER)

Andrew Clark
www.premiersoccerstats.com

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2010-02-01 : 21:19:29

Remove ')' after first INNER join

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-02-01 : 22:20:21

That just gives Invalid object name 'tblMatch'.

Kristen's solution does work

Andrew Clark
www.premiersoccerstats.com

Kristen
Test

22859 Posts

Posted - 2010-02-02 : 02:15:44

"Kristen's solution does work"

That's normally the way it is done, ghastly as it looks ...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-02 : 11:08:20

There were some extra parenthesis in my cut-n-paste.

Try this:

SELECT 
	TeamID,
	COUNT(TeamID) AS ShutOutAcheived,
	Season
FROM
	(
		SELECT  
			tblMatchTeam.TEAMID, 
			--Count(tblMatchTeam.TEAMID) AS ShutOutAcheived,
			case 
				when (tblMatch.[Date] > CONVERT(DATETIME,  '1992-08-01' , 102) AND tblMatch.[Date] < CONVERT (DATETIME,  '1993-08-01' , 102)  
					Then '1992/3' 
				when (tblMatch.[Date] > CONVERT(DATETIME,  '1993-08-01' , 102) AND tblMatch.[Date] < CONVERT (DATETIME,  '1994-08-01' , 102)  
					Then '1993/4' 
			end AS Season
		FROM 
			tblMatch 
		INNER JOIN 
			tblMatchTeam AS tblMatchTeam_1 
			ON tblMatch.MATCHID = tblMatchTeam_1.MATCHID
		INNER JOIN 
			tblMatchTeam 
			ON tblMatch.MATCHID = tblMatchTeam.MATCHID
		WHERE 
			tblMatchTeam_1.TEAMID <> [tblMatchTeam].[TEAMID] 
			AND tblMatchTeam_1.GOALS = 0
	) AS D	
GROUP BY  
	TEAMID, 
	Season

AndyC
Yak Posting Veteran

53 Posts

Posted - 2010-02-02 : 12:30:05

took out a couple of parenthses before tblMatch.[Date] and it succeeded
Your solution is a bit more succinct, thanks

Andrew Clark
www.premiersoccerstats.com

Kristen
Test

22859 Posts

Posted - 2010-02-02 : 12:55:53

Best to just check the performance of each though (I'm not sure which is better ...

)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources