Solved

Get AVG of SUM in MySQL with other SUMS

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Server group by 10 45
Connection to multiple databases 13 26
Populate custom post type/custom fields from an external database in WordPress 3 33
SQL Syntax 14 35
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

828 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