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:

SELECT CustID, 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 Profit15From vCustomerInvoiceDetail Where InvoiceDate Between '2015-1-1' and '2015-12-31'Group by CustIDOrder by Profit15 DescLimit 150

SELECT CustID, SUM(case when ItemCategory = 10 THEN PubProfit else 0 END) as ChemProfit,AVG(case when ItemCategory = 10 THEN PubProfit else 0 END) as ChemAvg,SUM(PubProfit) as Profit15From vCustomerInvoiceDetail Where InvoiceDate Between '2015-1-1' and '2015-12-31'Group by CustIDOrder by Profit15 DescLimit 150

Sarath, thank you very much for your response.
My issue is that I need the average of ChemProfit which it why I was trying to do a SUM inside the AVG.
This query just gives me the average of every line with a ItemCategory of 10.

SELECT CustID, SUM(case when ItemCategory = 10 THEN PubProfit else 0 END) as ChemProfit,SUM(case when ItemCategory = 10 THEN PubProfit else 0 END)/COUNT(DISTINCT CustID) as ChemAvg,SUM(PubProfit) as Profit15From vCustomerInvoiceDetail Where InvoiceDate Between '2015-1-1' and '2015-12-31'Group by CustIDOrder by Profit15 DescLimit 150

I think your error is in the order by clause.
Try this.

SELECT X.* FROM (SELECT CustID, 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 Profit15From vCustomerInvoiceDetail Where InvoiceDate Between '2015-1-1' and '2015-12-31'Group by CustID ) AS XOrder by X.Profit15 DescLimit 150

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Popularity Can Be Measured
Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients. This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online.
The email signature template has been downloaded from:
www.mail-signatures…

Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…