CODEWITHSUNDEEP
sqlsql-serversql-server-2008
mithderler
mithderler

Reputation: 33

Sum and group a column by conditional criteria

I have a table and want to use 'Sum Functions' by some criteria. For example:

ProductName     Type    Amount
-------------------------------
ProductA        2       10
ProductB        7       10
ProductA        8       10
ProductA        8       10
ProductC        7       10
ProductA        7       10
ProductA        3       10
ProductA        7       10
ProductB        3       10
ProductB        8       10

I want to group and sum each product with this formula:

Product Amount = Sum( (Type7 amount)+(Type8 amount)) - Sum( (Type2 amount)+(Type3 amount))

So, our example's query result must be:

ProductName     Amount
-----------------------
ProductA        20
ProductB        10
ProductC        10

I have a query which is working:

SELECT CL.DEFINITION_
    ,ST.TRCODE
    ,IT.NAME
    ,SUM(ST.AMOUNT) AS AMOUNT
    ,
FROM LG_060_CLCARD CL
    ,LG_060_ITEMS IT
    ,LG_060_04_STLINE ST
WHERE IT.LOGICALREF = ST.STOCKREF
    AND ST.CLIENTREF = CL.LOGICALREF
    AND ST.TRCODE IN (
        '2'
        ,'3'
        ,'7'
        ,'8'
        )
GROUP BY CL.DEFINITION_
    ,ST.TRCODE
    ,IT.NAME
    ,
ORDER BY CL.DEFINITION_
    ,ST.TRCODE
    ,IT.NAME

This query shows a table like our example's first form. I want to transform this query as our examples' second form.

ST.TRCODE is the column which is 'Type' in our example. How can I group products by 'Sum Function' criteria as I mentioned above?

Upvotes: 0

Views: 72

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do conditional summation : 

select t.ProductName, 
       sum(case when type in (7, 8) then amount when type in (2, 3) then -amount end) as Amount
from table t
group by ProductName;

Upvotes: 0

JJ32
JJ32

Reputation: 1034

select ProductName, 
  sum(case when type in ('7', '8') then 1 when type in ('2', '3') then -1 end * Amount) AS Amount
from MyTable
group by ProductName

Results

ProductName Amount
ProductA    20
ProductB    10
ProductC    10

Upvotes: 1

Related Questions