Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    142

    Unanswered: Average between differences

    Hi,

    I ave a field called vehicle_milage and it updates the current milage for example, 50145 for today and for tomorrow it's 51764 then the day after it's 54943 like this:

    50145
    51764
    54943
    54943
    58365
    68734

    I want to know how can I get the average between the difference of all to know what's the average milage my vehcile is driving per day?


    Thanks,
    Jassim

  2. #2
    Join Date
    Oct 2014
    Posts
    291
    Provided Answers: 7
    SELECT
    *
    ,AVG(MILEAGE) OVER (ORDER BY DATE ASC) AS ROLLING_AVG

    FROM
    #GASMILEAGE
    SELECT
    DATE
    ,MILEAGE
    ,AVG(MILEAGE) OVER (ORDER BY DATE ASC) AS ROLLING_AVG
    ,C.TOTAL_AVERAGE
    FROM
    #GASMILEAGE
    CROSS JOIN (SELECT (SUM(MILEAGE)/COUNT(*)) AS TOTAL_AVERAGE FROM #GASMILEAGE ) AS C

    GROUP BY
    DATE
    ,MILEAGE
    ,C.TOTAL_AVERAGE
    I added both, one with the rolling average date by date and the overall average from the total set of data.

    This should continue to average by date ascending order.

    Hope this helps.
    Last edited by VLOOKUP; 03-05-17 at 21:53.
    70-461 SQL Certified.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •