SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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:

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this:

    Code:
    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



  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    still no GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •