Group by issue.. - 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)

Group by issue..

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-11-25 : 09:02:42

in select statement i have 30 columns .. in this i have only 2 columns are aggregate .. other 28 are having case .... so do i need to add this 28 with case in group by clause ???? alias cant be used .. anyother way to simplyfy ????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 09:32:59

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 11:49:27

quote:
Originally posted by niranjankumark

in select statement i have 30 columns .. in this i have only 2 columns are aggregate .. other 28 are having case .... so do i need to add this 28 with case in group by clause ???? alias cant be used .. anyother way to simplyfy ????

its not required to add them to group by. if possible you could use derived tables which uses group by nad gives you aggregated result. then just join it with main query to get your result.
If you can provide your current query, we can show you how.

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-11-26 : 08:15:48

in select aggregate function will be used .. need to avoid group by and simplyfy the select clause .. to avoid more temporary table space...

SELECT DISTINCT CASE
WHEN NT.NT_SUB = 'SN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "SYNOPSIS",

CASE
WHEN NT.NT_SUB = 'CV'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "ALLEGED COVERAGE",

CASE
WHEN NT.NT_SUB = 'DMIN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "DESCRIPTION OF MATTERS & ISSUES",

CASE
WHEN NT.NT_SUB = 'STN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "STRATEGY",

CASE
WHEN NT.NT_SUB = 'NN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "NEGOTIATIONS",

CASE
WHEN NT.NT_SUB = 'UP'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "UPDATE SINCE LAST REPORT"
FROM TABLE1

group by CASE
WHEN NT.NT_SUB = 'SN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "SYNOPSIS",

CASE
WHEN NT.NT_SUB = 'CV'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "ALLEGED COVERAGE",

CASE
WHEN NT.NT_SUB = 'DMIN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "DESCRIPTION OF MATTERS & ISSUES",

CASE
WHEN NT.NT_SUB = 'STN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "STRATEGY",

CASE
WHEN NT.NT_SUB = 'NN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "NEGOTIATIONS",

CASE
WHEN NT.NT_SUB = 'UP'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "UPDATE SINCE LAST REPORT"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 08:19:22

quote:
Originally posted by niranjankumark

to avoid more temporary table space...

Where do you think the worktable used for DISTINCT is stored?

E 12°55'05.63"
N 56°04'39.26"

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-11-26 : 08:23:23

while executing the query temporary table space will be created , for distinct , group by and order will occupy ore space.. suppose this temp table space may not be enough .. execution error will come .. this kind of issue i am facing in DB2... so if i simply this kind of query .. i can rebuild there... like to avoid case with group by ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 08:25:24

GROUP BY is more likely to use less worktable space than DISTINCT.
The only way to be sure, is to write two queries; one with DISTINCT and one with GROUP BY.
Then compare execution plan, statistics io, profiler results and so on.

E 12°55'05.63"
N 56°04'39.26"

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-11-26 : 08:31:06

seperation is good idea... finally i need to show in single execution that i need to make join right ??? so distinct shud be used there right ???...

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-11-26 : 09:21:03

can i use the group by column like this ( one with case , and column alone ) .. wil the both result will be the same

SELECT DISTINCT CASE
WHEN NT.NT_SUB = 'SN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "SYNOPSIS",

CASE
WHEN NT.NT_SUB = 'CV'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "ALLEGED COVERAGE",

CASE
WHEN NT.NT_SUB = 'DMIN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "DESCRIPTION OF MATTERS & ISSUES",

CASE
WHEN NT.NT_SUB = 'STN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "STRATEGY",

CASE
WHEN NT.NT_SUB = 'NN'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "NEGOTIATIONS",

CASE
WHEN NT.NT_SUB = 'UP'
THEN TABLE1.VAR_TABLE1
ELSE ( '' )
END
AS "UPDATE SINCE LAST REPORT"
FROM TABLE1
group by NT.NT_SUB , TABLE1.VAR_TABLE1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 09:29:54

This is your original query. Where is NT table?

SELECT DISTINCT	CASE WHEN NT.NT_SUB = 'SN' THEN TABLE1.VAR_TABLE1 ELSE '' END AS [SYNOPSIS],
		CASE WHEN NT.NT_SUB = 'CV' THEN TABLE1.VAR_TABLE1 ELSE '' END AS [ALLEGED COVERAGE],
		CASE WHEN NT.NT_SUB = 'DMIN' THEN TABLE1.VAR_TABLE1 ELSE '' END AS [DESCRIPTION OF MATTERS & ISSUES],
		CASE WHEN NT.NT_SUB = 'STN' THEN TABLE1.VAR_TABLE1 ELSE '' END AS [STRATEGY],
		CASE WHEN NT.NT_SUB = 'NN' THEN TABLE1.VAR_TABLE1 ELSE '' END AS [NEGOTIATIONS],
		CASE WHEN NT.NT_SUB = 'UP' THEN TABLE1.VAR_TABLE1 ELSE '' END AS [UPDATE SINCE LAST REPORT]
FROM		TABLE1

E 12°55'05.63"
N 56°04'39.26"

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-11-26 : 09:54:15

missed out nt... my intension is group by can applicable like this ???

Will qry output will be the same ???? how will the exeution goes ???
only difference in group by ,,

PESO kindly reply ASAP

QRY 1
=========

SELECT CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END
SUM(T.C4) ,
SUM(T.C5)
FROM T1, T2
WHERE T1.C3 = T2.C3
GROUP BY CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END

QRY 2
=========

SELECT CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END ,
CASE WHEN T1.C1 = 'A' THEN T1.C2 END
SUM(T.C4) ,
SUM(T.C5)
FROM T1, T2
WHERE T1.C3 = T2.C3
GROUP BY T1.C1 , T1.C2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources