How can I use SQL SUM(DECODE... in Microsoft SQL Server 2005

Here's the code I run in Oracle SQL * Plus but I can't get the DECODE part to work in Microsoft SQL Server 2005, any ideas ?

drop table sales;

create table sales
(customer      varchar2(10),
 pos_type      varchar2(1),
qty      number(9),
 price      number(9)
)
;

insert into sales
values ('BOB','S','1','50.00')
;
insert into sales
values ('BOB','S','2','100.00')
;
insert into sales
values ('BOB','C','1','10.00')
;

select * from sales
;

CUSTOMER   P    QTY     PRICE
---------- - --------- ---------
BOB        S         1              50
BOB        S         2             100
BOB        C         1              10

When I run this sql it sums all the Pos Type S and takes away the SUM of Pos Type C

select      b.customer,
      sum(decode(b.pos_type,'S',b.qty,0)) - sum(decode(b.pos_type,'C',b.qty,0)),
      sum(decode(b.pos_type,'S',b.price,0)) - sum(decode(b.pos_type,'C',b.price,0))
from      sales b
group by b.customer
;

result that I need to get is:
CUSTOMER   QTY    RETAIL
---------- --------- ---------
BOB                2       140

Many Thanks,

Jason.
Start Free Trial