divide values in a Group By query

group-by, sql, sql-server, sql-server-2008

Solution

without recalculating...

SELECT *, A.TotalPrice / A.TotalQuantity AS AveragePrice
FROM (SELECT 
       CategoryId
       , SUM(Price) AS TotalPrice
       , SUM(Quantity) AS TotalQuantity
      FROM Products 
      GROUP BY CategoryId) AS A

Problem

How is it possible to build a request like this (Sql Server 2008 R2) : (I need an "Average Price" per result) ``` SELECT CategoryId , SUM(Price) AS TotalPrice , SUM(Quantity) AS TotalQuantity -->, TotalPrice/TotalQuantity AS AveragePrice FROM Products GROUP BY CategoryId ``` (if possible without recalculating one more times the two SUMs...)

Original source