Re: SQL question

Henrik Staun Poulsen wrote:

> Dear All,
>
> I want to select the sum of all orders, but I want to subtract the all
> the sale reversals, i.e. all orders that where canceled out.
> Do I need to create a union like
>
> create view test as
> select sum(amount) 'TotalO' from order where Type = 'S'
> UNION
> select (sum(amount) * (-1)) 'TotalO' from order where Type = 'R'
>
> select TotalO from test
>
> OR is there a way of writing the select statement with an IF statement
> or a CASE statement?
>
> TIA
>
> Henrik Staun Poulsen
> InfoLog

Well, I think that previous responses missed something. To do exactly what you would try this statement :

select sum(decode(type, 'R', amount * -1, 'S', amount, 0)) "Total0" from order
group by type;

If you have other values than 'S' or 'R' for type in your table and don't want them in the result, then try
this one :
select sum(decode(type, 'R', amount * -1, 'S', amount, 0)) "Total0" from order
where type in ('R', 'S')
group by type; Received on Thu Sep 10 1998 - 04:01:42 CDT