Solved

Get AVG of SUM in MySQL with other SUMS

Posted on 2016-09-14
5
55 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
  • 2
  • 2
5 Comments
 
LVL 40

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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

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.

Question has a verified solution.

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

Suggested Solutions

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…

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now