CODEWITHSUNDEEP
mysql
MOZ
MOZ

Reputation: 758

MySQL query - Invalid Use of Group Function - Using SUM

I am having "Invalid use of group function" error while executing following query.

Select id, faultdistribution, faulttype, faultseverity, 
SUM(IF (faultdistribution='crs', SUM(IF(faultdistribution='crs',1,0))*8, 0)+
IF (faultdistribution='configuration', SUM(IF(faultdistribution='configuration',1,0))* 6, 0)+
IF (faulttype='business' AND faultseverity='fatal', SUM(IF(faulttype='business' AND faultseverity='fatal',1,0))* 4, 0)+
IF (faulttype='business' AND faultseverity='major', SUM(IF(faulttype='business' AND faultseverity='major',1,0))* 2, 0)+
IF (faulttype='business' AND faultseverity='moderate', SUM(IF(faulttype='business' AND faultseverity='moderate',1,0))* 5, 0)+
IF (faulttype='business' AND faultseverity='minor', SUM(IF(faulttype='business' AND faultseverity='minor',1,0))* 3, 0)+
IF (faulttype='look & feel' AND faultseverity='fatal', SUM(IF(faulttype='look & feel' AND faultseverity='fatal',1,0))* 2, 0)+
IF (faulttype='look & feel' AND faultseverity='major', SUM(IF(faulttype='look & feel' AND faultseverity='major',1,0))* 1, 0))
FROM tbl_fault WHERE product='DAS' AND faultdistribution='missed'

Where i am doing it wrong? Kindly help!

Upvotes: 2

Views: 4867

Answers (2)

echo_Me
echo_Me

Reputation: 37233

try this shortened query

 Select id, faultdistribution, faulttype, faultseverity, 
 IF (faultdistribution='crs', 1,0)*8 +
 IF (faultdistribution='configuration', 1,0)* 6 +
 IF (faulttype='business' AND faultseverity='fatal', 1,0)* 4 +
 IF (faulttype='business' AND faultseverity='major', 1,0)* 2 +
 IF (faulttype='business' AND faultseverity='moderate', 1,0)* 5 +
 IF (faulttype='business' AND faultseverity='minor', 1,0)* 3 +
 IF (faulttype='look & feel' AND faultseverity='fatal', 1,0)* 2 +
 IF (faulttype='look & feel' AND faultseverity='major', 1,0)* 1 as mysum
 FROM tbl_fault WHERE product='DAS' AND faultdistribution='missed'
 group by id , faultdistribution ,faulttype ,faultseverity

Upvotes: 1

Dan
Dan

Reputation: 10680

SUM(), COUNT(), AVG(), MIN(), MAX(), etc. are aggregate functions that requires you to specify a GROUP BY, unless you're using them on every column in your SELECT-list.

In your case, the query should work by adding the following at the bottom:

GROUP BY id, faultdistribution, faulttype, faultseverity

...but judging from the many nested IF's you have, I'm not entirely sure that this would give you the output you're looking for.

Upvotes: 4

Related Questions