SQL "CASE" Help

• npeters 86796

  Old Hand

  Points: 310

  More actions

  January 5, 2011 at 6:04 pm

  #243226

  i have a query with the following case statement.

  SUM(CASE WHEN Department = 'SOUH' THEN TrxFullPrice

  ELSE 0

  END) AS SOUH

  i need to add multiple departments however i cannot get it to work.

  something like this.

  SUM(CASE WHEN Department = 'SOUH', 'DIAM', 'JEWR' THEN TrxFullPrice

  ELSE 0

  END) AS SOUH

  can someone please let me know if this is possible?

  or provide a workaround?

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  January 5, 2011 at 6:37 pm

  #1269833

  Is this what you require?

  CREATE TABLE #T(Department VARCHAR(20),TrxFullPrice MONEY)

  INSERT INTO #T

  SELECT 'Souh',100.00 UNION ALL

  SELECT 'Diam',110.00 UNION ALL

  SELECT 'Jewr',120.00 UNION ALL

  SELECT 'Nuts',500

  SELECT SUM(TrxFullPrice) FROM #T

  WHERE Department IN('SOUH', 'DIAM', 'JEWR')

  Result is $330.00

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• npeters 86796

  Old Hand

  Points: 310

  More actions

  January 5, 2011 at 6:59 pm

  #1269836

  No this will not work.

  maybe doing a union on separate case statements?

• npeters 86796

  Old Hand

  Points: 310

  More actions

  January 5, 2011 at 7:04 pm

  #1269838

  Thank you for the person who gave me the answer;

  SUM(CASE WHEN Department in ('SOUH', 'DIAM', 'JEWR') THEN TrxFullPrice

  ELSE 0

  END) AS SOUH