Converting Query Results to Negative Numbers -- Totalling Results

• Lana Underwood

  Old Hand

  Points: 338

  More actions

  February 24, 2005 at 10:15 pm

  #185374

  I was given the following example to convert data to negative numbers in a given condition:

  SELECT RecordType, CASE WHEN RecordType = 20 THEN (Quantity_Ord * -1)

  ELSE Quantity_Ord END AS Quantity_Ordered

  FROM TABLENAME

  It works great, but I also need to total the results.  When I tell it to sum the quantity_ordered column, SQL keeps giving me an error that the Sum function requires 1 argument.  I have tried using compute, but the query builder I am using does not allow you to manipulate the code and place it where you need it to go.  It builds the code for you based on the questions you answer.  Is there another way to get the columns to sum that can be placed within the original example above?

   Thanks.

• Dinesh Asanka

  SSChampion

  Points: 11058

  More actions

  February 25, 2005 at 12:53 am

  #544076

  select sum (a) from (select sum(amount* -1) as a from TABLENAME where RecordType = 20 union all

  select sum(amount) as a from table1 where RecordType <> 20) a

   

  will give you the sum

  
  
  
  

  My Blog: http://dineshasanka.spaces.live.com/