Wrong sum output from case statement condition

Hi,

I have data:
shift_id = 1---2---3---4---5
compound_output = 5---10--20--50--10
process_id = 7
compound_type = P28

and now I need to create a query that sum the value of (1+3+(5/ 2)) and (2+4+(5/ 2))

here is my query:

SELECT process_id, compound_type, CASE WHEN (shift_id IS NOT NULL AND (shift_id = 1 OR shift_id = 3 OR shift_id = 5)) 
THEN SUM((compound_output)/2)
when (shift_id IS NOT NULL AND (shift_id = 2 OR shift_id = 4 OR shift_id = 5)) 
THEN SUM((compound_output)/2)  END AS val
FROM op_output WHERE process_id = 7 and compound_type = 'P28' HAVING val IS NOT NULL 

Code (markup):

and the output of this is:

val = 47.500000

but it should be:
7---P28--30
7---P28--65


Thank you so much..


I hope somebody can help me to get the correct values.

Thank you so much.

 

Looking into your solution. But the reason why this is happening. Your logic is all wrong. You are summing up all values in compound_output with

THEN SUM((compound_output)/2)
So you are in effect getting (5+10+20+50+10)/2 and that is (95)/2 = 47.5
Another logic bomb you have is that you have shift_id = 5 in both cases. So no matter what it is always true for both cases. Though the answer is always going to be overwritten by the later case.

 

Also.. You have this: (1+3+(5/ 2))

Did you want this: (1+3+5)/ 2)) like you have it in MYSQL statement you supplied, or like you have it in the equation you supplied?

And... it would help to have your tables structure.