select
date,
item,
sum(amount)
from mytable
group by date,item,
(
case when amount > 0 THEN 1
ELSE
0
END
)

Adomas
moegie2 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
>
>Many Thanx
>
>
>
>
>
>
>
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>