Wrong sum output from case statement condition

Databases
1

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.

2

SUM() is an aggregate function and won’t work without a GROUP BY clause

look at your two THEN results… they are the same, so you don’t really need a CASE at all

3

I tried this:


SELECT process_id, compound_type, IF(shift_id = 1 OR shift_id = 3 OR shift_id = 5, SUM(compound_output), 0) AS output1,  IF(shift_id = 2 OR shift_id = 4 OR shift_id = 5, SUM(compound_output), 0) AS output2  FROM op_output WHERE process_id = 7 and compound_type = 'P28'

and the result:

7----P28—95.00—0.00

:confused:

4

still no GROUP BY clause