CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ayman
Ayman

Reputation: 872

SQL - query to get Avg of a Sum

Question:
How can I get avg of Sum(od.UnitPrice * od.Quantity)

The query: 

SELECT 
    YEAR(o.OrderDate) as [Sales Year],
    MONTH(o.OrderDate) as [Sales Month],
    SUM(CASE WHEN c.catid = 1 THEN od.UnitPrice * od.Quantity ELSE 0 END) AS [Dispenser Sales],
    SUM(CASE WHEN c.catid = 2 THEN od.UnitPrice * od.Quantity ELSE 0 END) AS [Refill Sales]
FROM 
    Orders o
JOIN
    orderdetails od ON o.order_id = od.orderid
JOIN 
    store s ON s.pro_id = od.Proid
JOIN
    category c ON c.catid = s.catid
GROUP BY 
    YEAR(o.OrderDate), MONTH(o.OrderDate)
ORDER BY 
    YEAR(o.OrderDate), MONTH(o.OrderDate)

Upvotes: 0

Views: 99

Answers (2)

David Bachmann Jeppesen
David Bachmann Jeppesen

Reputation: 395

Use avg(od.UnitPrice * od.Quantity) instead of Sum(od.UnitPrice * od.Quantity)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

If I understand correctly, you can use avg(), but presumably you don't want the missing values to be 0. So, leave out the else clause or be really verbose and use else NULL:

SELECT YEAR(o.OrderDate) as [Sales Year],
       MONTH(o.OrderDate) as [Sales Month],
       AVG(case when c.catid = 1 then od.UnitPrice * od.Quantity end) AS [Dispenser Sales],
       AVG(case when c.catid = 2 then od.UnitPrice * od.Quantity end) AS [Refill Sales]
FROM . . .

Another possibility is that you want the overall average. If so, you can use window functions to append the average to each row. A subquery/CTE makes this easier:

SELECT t.*,
       AVG(DispenserSales) OVER () as avg_DispenserSales,
       AVG(RefillSales) OVER () as avg_RefillSales
FROM (SELECT YEAR(o.OrderDate) as SalesYear,
             MONTH(o.OrderDate) as SalesMonth,
             SUM(case when c.catid = 1 then od.UnitPrice * od.Quantity end) AS DispenserSales,
             SUM(case when c.catid = 2 then od.UnitPrice * od.Quantity end) AS RefillSales
      FROM . . .
     ) t

Upvotes: 1

Related Questions