How can I calculate SQL Average?

Posted on 2012-08-23
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!
Question by:EffinGood
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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
    LVL 12

    Expert Comment

    by:Habib Pourfard
            DATEDIFF(d, DateStart, DateFinish) BakeTime
    FROM    YourTable
    SELECT  'Average Bake Time', NULL, NULL, AVG(CAST(DATEDIFF(d, DateStart, DateFinish) AS FLOAT))
    FROM    YourTable

    Open in new window

    LVL 39

    Accepted Solution

    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.

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

    Open in new window


    Author Closing Comment

    Excellent work. Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now