CODEWITHSUNDEEP
sql
sql scholar
sql scholar

Reputation: 199

SQL SUM and value conversion

I'm looking to transform data in my SUM query to acknowledge that some numeric values are negative in nature, although not represented as such.

I look for customer balance where the example dataset includes also credit transactions that are not written as negative in the database (although all records that have value C for credit in inv_type column should be treated as negative in the SQL SUM function). As an example:

INVOICES
    inv_no  inv_type   cust_no  value

      1        D          25    10
      2        D          35    30
      3        C          25    5
      4        D          25    50
      5        C          35    2

My simple SUM function would not give me the correct answer:

select cust_no, sum(value) from INVOICES
group by cust_no

This query would obviously sum the balance of customer no 25 for 65 and no 35 for 32, although the anticipated answer would be 10-5+50 = 55 and 30 - 2 = 28

Should I perhaps utilize CAST function somehow? Unfortunately I'm not up to date on the underlying db engine, however good chance of it being of IBM origin. Most of the basic SQL code has worked out so far though.

Upvotes: 0

Views: 241

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use the case expression inside of a sum(). The simplest syntax would be:

select cust_no,
       sum(case when inv_type = 'C' then - value else value end) as total
from invoices
group by cust_no;

Note that value could be a reserved word in your database, so you might need to escape the column name.

Upvotes: 1

Paul Williams
Paul Williams

Reputation: 17020

You can put an expression in the sum that calculates a negative value if the invoice is a credit:

select
    cust_no,
    sum
    (
        case inv_type
            when 'C' then -[value]
            else [value]
        end
    ) as [Total]
from INVOICES

Upvotes: 0

muratgu
muratgu

Reputation: 7311

You should be able to write a projection (select) first to obtain a signed value column based on inv_type or whatever, and then do a sum over that.

Like this:

select cust_no, sum(value) from (
  select cust_no
       , case when inv_type='D' then [value] else -[value] end [value]    
    from INVOICES
) SUMS
group by cust_no

Upvotes: 0

Related Questions