Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic Grouping Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 4:28 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 2:20 AM
Points: 769, Visits: 847
Hey all,

I had a query that i didnt like - as it was a pain to maintain. Basically there are 6 levels of grouping that can be requested by a stored proc. So i had

IF @level =1
begin
select ....
end
ELSE IF @level=2
begin
select .....
end

I am sure you get the idea. Well i wondered if could do something like this


                SELECT  metricind
,LegalEntity
,SLKBU
,SLKDepartment
,SLKTeam
,CASE WHEN @Level = 6 THEN AssignedHandler ELSE '' END AS AssignedHandler
,Banding
,BandingOrder
,SUM(LedgerAmountGBP) AS LedgerAmountGBP
,SUM(Records) AS Records
,SLKTeam AS DashboardText
FROM DATATable
GROUP BY LegalEntity
,SLKBU
,SLKDepartment
, SLKTeam
,CASE WHEN @Level < 6 THEN AssignedHandler ELSE '' END
,metricind
,Banding
,BandingOrder


Obviously i would then move onto other columns for 5-1.... but this was a test.

However i am getting back

Column 'DATATable.AssignedHandler' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Now i read a couple of forum posts which seem to suggest you can do what i have asked.

So my question - can you do this (was the forum poster incorrect) and if so what am i doing wrong.

Else the only other way i can think of is building the SQL query. Dont fancy that either TBH

Any help greatfully received.

Dan


Post #1412340
Posted Monday, January 28, 2013 5:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:19 AM
Points: 1,980, Visits: 3,346
Your CASE statements need to match in the select & group by clauses - you have @Level = 6 in the select, and @Level < 6 in the group by.
Post #1412366
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse