Re: Simple question about SUM, but I can't figure it out

Dwayne King wrote:
>
> Hi there.
>
> I have what seems like a simple problem, but can't seem to figure out how to
> solve it.
>
> I have a table with one column containing a sum, and the other specifying if it
> is a debit or a credit (+ or -). Now what I want to do is use the SUM command
> to total the column, but I need some way of telling SUM to treat certain values
> as debits, and certain others as credits.
>
> Of course I can do this in two selects like below, but I would prefer one.
>
> SQL> select sum(col2) from test where col1 in (1,2); <----means credit
>
> SUM(COL2)
> ---------
> 3
>
> SQL> select sum(-col2) from test where col1 in (3,4,5); <----means debit
>
> SUM(-COL2)
> ----------
> -12
>
> Should I be using DECODE here? Will I have to use the two select method?
>
> Thanks ahead of time. Please email as well as post - having trouble with
> the news server.
>
> ---
> *****************************************************************************
> * Dwayne K. King * email: dwayne_at_dwayne.freenet.kiev.ua *
> * International Monetary Fund * phone: (011-380-44) 212-54-81 *
> *****************************************************************************

Dwayne,

You should be able to achieve the result you want here by using decode. You'll probably want to use two decode statements in your query, one for  credit and one for debit.

hope this helps,

regards,
Chuck

-- 
*****************************************************************************
*
*   Chuck Wolfe                         Phone: (703)838-9720
*   Senior Consultant                   e-mail:  cwolfe_at_ix.netcom.com 
(Home)
*   NDC Group, Inc                               cwolfe_at_ndcinc.com    
(Work)
*   625 Slaters Lane, 
*   Suite 102
*   Alexandria, VA 22314
* 
*   All the usual discalimers apply, except where otherwise indicated...
****************************************************************************