I have a query where I need to display a Customer ID, Total Profit from a product category, and the avg total profit for the top 150 customers (by total profit).
My query looks like this:
SUM(case when ItemCategory = 10 THEN PubProfit else 0 END) as ChemProfit,
AVG(SUM(case when ItemCategory = 10 THEN PubProfit else 0 END)) as ChemAvg,
SUM(CASE When InvoiceDate between '2015-1-1' and '2015-12-31' THEN PubProfit ELSE 0 END) as Profit15
Where InvoiceDate Between '2015-1-1' and '2015-12-31'
Group by CustID
Order by Profit15 Desc
But I get Invalid use of group function avg error. Can someone point me in the right direction on how to get this average?