EffinGood
asked on
SQL Grouping on last row - how do I do this?
Hello experts!
I have the following table, called tblBakingTimes:
PieBatchID DateStart DateFinish
1 09/1/2011 09/2/2011
2 09/3/2011 09/5/2011
3 09/6/2011 09/7/2011
With experts help, I've created a query that creates this:
Pie Batch DateStart DateFinish BakeTime
Average Bake Time 1.x
1 09/1/2011 09/2/2011 1
2 09/3/2011 09/5/2011 2
3 09/6/2011 09/7/2011 1
But what I want is this instead:
Pie Batch DateStart DateFinish BakeTime
1 09/1/2011 09/2/2011 1
2 09/3/2011 09/5/2011 2
3 09/6/2011 09/7/2011 1
Average Bake Time 1.x
Here's my query:
How do I get that average on the last line? I'll need to be able to order by anything, not just PieBatchID, so my solution has to be independent of whatever is in Order By.
Thanks experts, you're the best.
I have the following table, called tblBakingTimes:
PieBatchID DateStart DateFinish
1 09/1/2011 09/2/2011
2 09/3/2011 09/5/2011
3 09/6/2011 09/7/2011
With experts help, I've created a query that creates this:
Pie Batch DateStart DateFinish BakeTime
Average Bake Time 1.x
1 09/1/2011 09/2/2011 1
2 09/3/2011 09/5/2011 2
3 09/6/2011 09/7/2011 1
But what I want is this instead:
Pie Batch DateStart DateFinish BakeTime
1 09/1/2011 09/2/2011 1
2 09/3/2011 09/5/2011 2
3 09/6/2011 09/7/2011 1
Average Bake Time 1.x
Here's my query:
Select
case when grouping(PieBatchID)=1 then 'Average Bake Time' else cast(PieBatchID as varchar(8)) end 'Pie Batch',
DateStart,
DateFinish, avg(BakeTime) as 'Bake Time'
From (SELECT PieBatchID ,
DateStart,
DateFinish,
DATEDIFF(d, DateStart, DateFinish)*1.0 BakeTime
FROM PieTable ) a
GROUP BY PieBatchID,
DateStart,
DateFinish WITH ROLLUP
having grouping(PieBatchID)=1
or (grouping(PieBatchID)=0 and grouping(DateStart)=0 and grouping(DateFinish)=0 )
order by PieBatchID;
How do I get that average on the last line? I'll need to be able to order by anything, not just PieBatchID, so my solution has to be independent of whatever is in Order By.
Thanks experts, you're the best.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER