--- In firebird-support@yahoogroups.com, "moegie2" <alexanderm@c...>
wrote:

>
> 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

try adding an index to

(amount, date, item)

Adam