CODEWITHSUNDEEP
mysql
MOZ
MOZ

Reputation: 758

MySQL Query for multiplication, addition and division

I am trying to formulate a query listed below; 

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

What i intend to do is add all or any one or two IF conditions that are true. Please also visit http://www.sqlfiddle.com/#!2/d2aac/44 . Need help!

Regards

Upvotes: 1

Views: 263

Answers (1)

juergen d
juergen d

Reputation: 204924

Select SUM(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'

Upvotes: 2

Related Questions