Wrong sum output from case statement condition

[newphpcoder]

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

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.

 

[Barand]

something like this?

SELECT
SUM(IF(shift_id IN (1,3,5), IF(shift_id=5, compound_output/2,compound_output),0))
as val1,
SUM(IF(shift_id IN (2,4,5), IF(shift_id=5, compound_output/2,compound_output),0))
as val2
FROM op_output;