Link to home
Start Free TrialLog in
Avatar of EffinGood
EffinGoodFlag for United States of America

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:

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;
                     

Open in new window


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
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EffinGood

ASKER

Cool. Thanks!