?
Solved

How can I calculate SQL Average?

Posted on 2012-08-23
4
Medium Priority
?
572 Views
Last Modified: 2012-08-28
SQL Averages and Last Row
Asked by: EffinGood
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

I want to create a query that will give me this:
PieBatchID  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


I know I can do a datediff for between start/finish to get BakeTime, but how could I create a final line that says "Average Bake Time" and add that number to the BakeTime column?

Thank you!
0
Comment
Question by:EffinGood
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38328003
the AVG function is also correct on sql server, except that you will need to UNION ALL the query that returns that last line.

however, you will run into data type issue etc.
best is to solve this in the reporting tool itself
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38328049
SELECT  CAST(PieBatchID AS VARCHAR(16)),
        DateStart,
        DateFinish,
        DATEDIFF(d, DateStart, DateFinish) BakeTime
FROM    YourTable
UNION ALL
SELECT  'Average Bake Time', NULL, NULL, AVG(CAST(DATEDIFF(d, DateStart, DateFinish) AS FLOAT))
FROM    YourTable

Open in new window

0
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 38328123
in a simillar situation in one of our systems used a query like this using rollup operator. may be not efficient but it works using single query.

Select 
case when grouping(PieBatchID)=1 then 'Average Bake Time' else PieBatchID end PieBatchID,
DateStart,
        DateFinish, avg(BakeTime) Baker
From (SELECT  PieBatchID ,
        DateStart,
        DateFinish,
        DATEDIFF(d, DateStart, DateFinish)*1.0 BakeTime
FROM    TableName ) a
GROUP BY PieBatchID,
DateStart,
        DateFinish WITH ROLLUP
having grouping(PieBatchID)=1 
or (grouping(PieBatchID)=0 and grouping(DateStart)=0 and grouping(DateFinish)=0  )

Open in new window

0
 

Author Closing Comment

by:EffinGood
ID: 38343782
Excellent work. Thank you.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question