"OKLover" <OKLo***@discussions.microsoft.com> wrote in message
news:118B260E-D42B-4859-8AA5-130B454265F6@microsoft.com...
> I have one table as below:
>
> item_id amount inout
> ----------------------------------------------------------------
> 1 13 1
> 1 20 1
> 1 7 0
> 1 24 1
> 2 10 0
> 2 20 1
>
> How to use the SELECT command to SUM the amounts according the inout (when
1
> is +, 0 is -) and item_id field?
>
> For example, when i give the item_id parameter with 1, it will returns the
> result of 50.  (13+20-7+24)
>
>

"Adam Machanic" wrote:

> Why not store the amounts as negative when they're supposed to be negative?
>
> SELECT
>     item_id,
>     SUM
>     (
>         CASE inout
>             WHEN 1 THEN amount
>             ELSE (amount * -1)
>         END
>     ) AS theSum
> GROUP BY item_id
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>
> "OKLover" <OKLo***@discussions.microsoft.com> wrote in message
> news:118B260E-D42B-4859-8AA5-130B454265F6@microsoft.com...
> > I have one table as below:
> >
> > item_id amount inout
> > ----------------------------------------------------------------
> > 1 13 1
> > 1 20 1
> > 1 7 0
> > 1 24 1
> > 2 10 0
> > 2 20 1
> >
> > How to use the SELECT command to SUM the amounts according the inout (when
> 1
> > is +, 0 is -) and item_id field?
> >
> > For example, when i give the item_id parameter with 1, it will returns the
> > result of 50.  (13+20-7+24)
> >
> >
>
>
>