Solved

# How can I calculate SQL Average?

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

LVL 143

Expert Comment

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

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
``````
0

LVL 39

Accepted Solution

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  )
``````
0

Author Closing Comment

ID: 38343782
Excellent work. Thank you.
0

## Featured Post

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
Course of the Month15 days, 3 hours left to enroll