Android Question SQL table

[manuelsalazar]

Hi:
Is there any SQL SELECT command, to obtain from a table the SUM and REST from a field , depend of another field, for example

Table1 (PRODUCT, QUANTITY , TYPE)

PRODUCT-A , 3 , P
PRODUCT-A , 2 , P
PRODUCT-A , 1 , N
PRODUCT-B , 3 , P
PRODUCT-B , 4 , P

WHERE TYPE=P (POSITIVE)
TYPE=N (NEGATIVE)

and group by PRODUCT

and obtain the RESULT as (PRODUCT , TOTAL-QUANTITY)

PRODUCT-A , 4
PRODUCT-B. , 7

it means PRODUCT-A. (3+2-1 = 4)
PRODUCT-B. (3+4 = 7)

THANKS IN ADVANCED
MANUEL

 

[Alex_197]

If I understood you correctly - I would do this

SELECT  Product ,
        SUM(CASE WHEN Type = 'P' THEN Quantity
                 ELSE Quantity * -1
            END) AS Quantity
FROM    tblProducts
GROUP BY Product;

Product Quantity
PRODUCT-A 4
PRODUCT-B 7

 

[manuelsalazar]

I forgot that also in TYPE field have also the Letter 'C' and also Letter 'D', so those records don't have to SUM or REST.

Thanks in advanced






Si

 

[Alex_197]

I forgot that also in TYPE field have also the Letter 'C' and also Letter 'D', so those records don't have to SUM or REST.

Thanks in advanced






Si

So in this case it will be

SELECT  Product ,

        SUM(CASE WHEN Type = 'P' THEN Quantity

                 WHEN Type = 'N' THEN Quantity * -1

                 ELSE 0

            END) AS Quantity

FROM    tblProducts

GROUP BY Product;

 

[manuelsalazar]

If I understood you correctly - I would do this

SELECT  Product ,
        SUM(CASE WHEN Type = 'P' THEN Quantity
                 ELSE Quantity * -1
            END) AS Quantity
FROM    tblProducts
GROUP BY Product;

Product Quantity
PRODUCT-A 4
PRODUCT-B 7