Post reply

case when sum

  • April 24, 2017 at 1:55 am

    #323127

    how can i sum into 1 row.

    total | menu1    | menu2
    30    | chicken  | fish

    i would like to join another table to do checking. i did as below but i get double up total. anything wrong with my query below?

    select total,menu1,menu2
    ,case when (menu1=tableB.code OR menu2=tableB.code) then sum(tableB.total) end as checking
    from tableA
    join tableB on tableA.ID=tableB.ID
    where menu1=tableB.code OR menu2=tableB.code
    group by 1,2,3,tableB.code

  • April 24, 2017 at 2:06 am

    #1939495

    With no consumable data, here's an example of how you would use SUM and CASE together:
    CREATE TABLE #Sample
      (menu1 varchar(10),
      menu2 varchar(10),
      total int);
    GO

    INSERT INTO #Sample
    VALUES
      ('chicken', 'fish', 30),
      ('chicken', 'steak', 10),
      ('ham', 'fish', 15),
      ('ham', 'steak', 40),
      ('salad', 'cous cous', 30);
    GO

    SELECT SUM(CASE WHEN menu1 = 'chicken' OR menu2 = 'fish' THEN total ELSE 0 END) AS Checking
    FROM #Sample;
    GO

    DROP TABLE #Sample;
    GO
     

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply