MySQL query - Invalid Use of Group Function - Using SUM

Question

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!

Is id the unique key for that table? If so how do you want to group things up over rows? — Kickstart
– Kickstart, Commented Oct 7, 2013 at 10:50

Dan · Accepted Answer · 2013-10-07 10:42:45Z

4

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.

answered Oct 7, 2013 at 10:42

Dan

10.8k27 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

echo_Me · Accepted Answer · 2013-10-07 11:10:22Z

1

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

answered Oct 7, 2013 at 11:10

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

5 Comments

echo_Me Over a year ago

@OmerZia try this shortened query

MOZ Over a year ago

that's a great help! :)

MOZ Over a year ago

Here is the schema : sqlfiddle.com/#!2/d2aac/19 While executing this query in PHP i am having 46 as a result. The result should be 6

echo_Me Over a year ago

i see just entries in your demo , can you put the whole data , so i can look at it ?

echo_Me Over a year ago

i mean where is 46 results , fill them by your data, in your example there is only 2 entries, so i dont know if it will get 6 results.

Collectives™ on Stack Overflow

MySQL query - Invalid Use of Group Function - Using SUM

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related