Solved

Get AVG of SUM in MySQL with other SUMS

Posted on 2016-09-14
5
74 Views
Last Modified: 2016-09-15
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 Profit15
From vCustomerInvoiceDetail 
Where InvoiceDate Between '2015-1-1' and '2015-12-31'
Group by CustID
Order by Profit15 Desc
Limit 150

Open in new window


But I get Invalid use of group function avg error. Can someone point me in the right direction on how to get this average?
0
Comment
Question by:dannyg280
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 41798517
try this.
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 Profit15
From vCustomerInvoiceDetail 
Where InvoiceDate Between '2015-1-1' and '2015-12-31'
Group by CustID
Order by Profit15 Desc
Limit 150

Open in new window

0
 

Author Comment

by:dannyg280
ID: 41798670
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.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 41798860
May be this.
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 Profit15
From vCustomerInvoiceDetail 
Where InvoiceDate Between '2015-1-1' and '2015-12-31'
Group by CustID
Order by Profit15 Desc
Limit 150

Open in new window

0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 41799752
Hi!

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 Profit15
From vCustomerInvoiceDetail 
Where InvoiceDate Between '2015-1-1' and '2015-12-31'
Group by CustID ) AS X
Order by X.Profit15 Desc
Limit 150

Open in new window


Regards,
     Tomas Helgi
0
 

Author Closing Comment

by:dannyg280
ID: 41799797
Thank You!
0

Featured Post

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question