Microsoft SQL Server OVER Clause Using Aggregation functions with OVER
Example
Using the Cars Table, we will calculate the total, max, min and average amount of money each costumer spent and haw many times (COUNT) she brought a car for repairing.
Id CustomerId MechanicId Model Status Total Cost
SELECT CustomerId,
SUM(TotalCost) OVER(PARTITION BY CustomerId) AS Total,
AVG(TotalCost) OVER(PARTITION BY CustomerId) AS Avg,
COUNT(TotalCost) OVER(PARTITION BY CustomerId) AS Count,
MIN(TotalCost) OVER(PARTITION BY CustomerId) AS Min,
MAX(TotalCost) OVER(PARTITION BY CustomerId) AS Max
FROM CarsTable
WHERE Status = 'READY'
Beware that using OVER in this fashion will not aggregate the rows returned. The above query will return the following:
| CustomerId | Total | Avg | Count | Min | Max |
|---|---|---|---|---|---|
| 1 | 430 | 215 | 2 | 200 | 230 |
| 1 | 430 | 215 | 2 | 200 | 230 |
The duplicated row(s) may not be that useful for reporting purposes.
If you wish to simply aggregate data, you will be better off using the GROUP BY clause along with the appropriate aggregate functions Eg:
SELECT CustomerId,
SUM(TotalCost) AS Total,
AVG(TotalCost) AS Avg,
COUNT(TotalCost) AS Count,
MIN(TotalCost) AS Min,
MAX(TotalCost) AS Max
FROM CarsTable
WHERE Status = 'READY'
GROUP BY CustomerId
