Subject | Summiing Amounts |
---|---|
Author | moegie2 |
Post date | 2005-11-30T14:47:27Z |
How do I sum values and still be able to separate positive and
negative values as two separate values
I have a table ... erm to simplify date,item,amount (which can + and -
values)
sample records
1/1/2005,item1,10
1/1/2005,item1,20
1/1/2005,item1,-5
1/1/2005,item2,10
I want to see
Date Item Total Credits Extended
==== ==== ===== ======= ========
1/1/2005 item1 30 5 25
1/1/2005 item2 10 0 10
I could use a union and a view round the union but it would be slow
Any ideas
select date,item,sum(amount) posamt,0 negamt
from file
where amount > 0
group by date,item
union
select date,itemp,0, sum(amount) negamt
from file
where amount < 0
group by date,item
Many Thanx
negative values as two separate values
I have a table ... erm to simplify date,item,amount (which can + and -
values)
sample records
1/1/2005,item1,10
1/1/2005,item1,20
1/1/2005,item1,-5
1/1/2005,item2,10
I want to see
Date Item Total Credits Extended
==== ==== ===== ======= ========
1/1/2005 item1 30 5 25
1/1/2005 item2 10 0 10
I could use a union and a view round the union but it would be slow
Any ideas
select date,item,sum(amount) posamt,0 negamt
from file
where amount > 0
group by date,item
union
select date,itemp,0, sum(amount) negamt
from file
where amount < 0
group by date,item
Many Thanx