CODEWITHSUNDEEP
sqlsql-serveraverage
user4912958
user4912958

Reputation:

New Column with average by Date in SQL Server

I have this table:

OrganizationCode | CreatedDate              | TotalCost
102              |  2015-05-18 00:00:00.000 | 200
102              |  2015-05-18 00:00:00.000 | 550
102              |  2015-05-15 00:00:00.000 | 700
102              |  2015-05-15 00:00:00.000 | 1200
103              |  2015-01-27 00:01:12.720 | 1275,46
103              |  2015-01-27 00:01:12.720 | 1275,46 
103              |  2015-01-27 00:01:12.720 | 1275,46

And i need add two column:

Daily Average = Sum of the values by date

Daily Amount = Amount by Date

I want the result below:

OrganizationCode | CreatedDate      | TotalCost | Daily Average | Daily Amount    
-----------------|------------------|-----------|---------------|--------    
102              | 2015-05-18 00:00 | 200       | 375           |  2    
102              | 2015-05-18 00:00 | 550       | 375           |  2    
102              | 2015-05-15 00:00 | 700       | 950           |  2    
102              | 2015-05-15 00:00 | 1200      | 950           |  2    
103              | 2015-01-27 00:01 | 1275,46   | 1275,46       |  3    
103              | 2015-01-27 00:01 | 1275,46   | 1275,46       |  3    
103              | 2015-01-27 00:01 | 1275,46   | 1275,46       |  3

I try this query:

   SELECT OrganizationCode 
          , CreatedDate 
          , (SELECT avg(TotalCost) AS x
                 FROM #TempLocalOrg
                 Group By CAST(CreatedDate as date)) 
   FROM #TempLocalOrg

No sucess, the sub select works, but I need to duplicate results

Upvotes: 2

Views: 145

Answers (2)

Gabriel Rainha
Gabriel Rainha

Reputation: 1743

SELECT OrganizationCode 
      , CreatedDate 
      , TotalCost
      , aggregates.AvgCost AS 'Daily Average '
      , aggregates.CountCost AS 'Daily Amount'
FROM #TempLocalOrg tmp
CROSS APPLY
(
   SELECT AVG(TotalCost) as AvgCost
         ,COUNT(1) AS CountCost
   FROM #TempLocalOrg crossTemp
   WHERE CAST(tmp.CreatedDate as date) = CAST(crossTemp.CreatedDate as date)
) aggregates

As you needed not to aggregate the values, we couldn't use group by. Instead, I used a CROSS APPLY to match every line's Date and aggregate it's results, as to apply it to every line distinct line. Feel free to contact me if you need any clarification on anything.

Upvotes: 0

David Faber
David Faber

Reputation: 12486

The best way to do this would be to use window functions:

SELECT OrganizationCode, CreatedDate
     , AVG(TotalCost) OVER ( PARTITION BY CreatedDate ) AS [Daily Average]
     , COUNT(*) OVER ( PARTITION BY CreatedDate ) AS [Daily Amount]
  FROM #TempLocalOrg;

The above assumes that dates are rounded or truncated to the nearest day.

Upvotes: 3

Related Questions