Solved

Get AVG of SUM in MySQL with other SUMS

Posted on 2016-09-14
5
82 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

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 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