Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

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.
0
EffinGood
Asked:
EffinGood
1 Solution
 
appariCommented:
try like this

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 case when grouping(PieBatchID)=1 then 1 else 0 end, PieBatchID;

Open in new window

0
 
EffinGoodAuthor Commented:
Cool. Thanks!
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now