[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 96
  • Last Modified:

Get AVG of SUM in MySQL with other SUMS

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
dannyg280
Asked:
dannyg280
  • 2
  • 2
1 Solution
 
SharathData EngineerCommented:
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
 
dannyg280Author Commented:
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
 
SharathData EngineerCommented:
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
 
Tomas Helgi JohannssonCommented:
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
 
dannyg280Author Commented:
Thank You!
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now