Hi Adomas

You put me absolutely on the right track ... i refined it to..

SELECT
ITEMNO,
sum(case when extended >= 0 then extended
else
0
end) Positive,
sum(case when extended < 0 then extended * -1
else
0
end) Negative
FROM
SALESITEMS
group by itemno

Thanx

--- In firebird-support@yahoogroups.com, Adomas Urbanavicius
<adomas@i...> wrote:

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