Sum a column into two different columns based on another column's value-CodePudding

What I need is only a list of the items in "Storage", but the resulting set should include the sum of that item's quantity in both the storage and active locations.

Here's a dataset example:

ID	Item	Location	Qty
1	ItemA	Storage	4
2	ItemA	Active	9
3	ItemB	Storage	3
4	ItemB	Storage	2
5	ItemA	Active	1
6	ItemC	Boxed	3
7	ItemD	Active	1
8	ItemD	Storage	1

The result would look like this:

Item	Storage	Active
ItemA	4	10
ItemB	5	0
ItemD	1	1

Note that ItemC should not be included because it is not in a valid location.

What I have tried so far is:

SELECT 
    ITEMDESC.A, 
    SUM(CASE WHEN LOCATION.A='Storage' THEN QTY.A ELSE 0 END), 
    SUM(CASE WHEN LOCATION.B='Active'  THEN QTY.B ELSE 0 END)
 FROM       
    ITEMS A, ITEMS B
 INNER JOIN     
    ITEMDESC.A = ITEMDESC.B
 WHERE      
 GROUP BY   
    ITEMDESC.A

but this returns ALL items listed. When I add something like "WHERE Location.B = 'Storage'" then it only sums the items in the storage and all the active location items are 0.

CodePudding user response：

Use a WHERE clause to only look at the locations in question:

select
  item,
  sum(case when location = 'Storage' then qty else 0 end) as storage,
  sum(case when location = 'Active' then qty else 0 end) as active
from items
where location in ('Storage', 'Active')
group by item
order by item;

Update

You have changed the desired output in your request and only want items that are in 'Storage'. For this, just add a HAVING clause, e.g.:

select
  item,
  sum(case when location = 'Storage' then qty else 0 end) as storage,
  sum(case when location = 'Active' then qty else 0 end) as active
from items
where location in ('Storage', 'Active')
group by item
having sum(case when location = 'Storage' then qty else 0 end) > 0
order by item;

CodePudding user response：

This should give you the desired results. The other answers are including values not in 'storage'

select 
  item,
  sum(case when location = 'Active' then qty else 0 end) as active_qty,
  sum(case when location = 'Storage' then qty else 0 end) as storage_qty
from *table*
where item in (select item from *table* where location = 'Storage')
group by item
order by item;

CodePudding user response：

Select  item,
        SUM(Case When Location = 'Storage' THEN Qty else 0 END) AS Storage,
        SUM(Case When Location = 'Active' THEN Qty else 0 END) AS Active 
from table1 
where location in ('Storage','Active')
GROUP BY Item

http://sqlfiddle.com/#!9/7339b9a/8

CodePudding user response：

You could use the WHERE clause in a subquery to identify items of interest and then JOIN to filter the rows prior to aggregation

SELECT 
    A.ITEMDESC, 
    SUM(CASE WHEN A.LOCATION='Storage' THEN A.QTY ELSE 0 END), 
    SUM(CASE WHEN A.LOCATION='Active'  THEN A.QTY ELSE 0 END)
 FROM       
    ITEMS A
 INNER JOIN
    (SELECT DISTINCT ITEMDESC FROM ITEMS WHERE LOCATION='Storage') B
 ON      
    A. ITEMDESC = B.ITEMDESC
 GROUP BY   
    A.ITEMDESC

Or you could filter the rows after aggregation with a HAVING clause

SELECT 
    ITEMDESC, 
    SUM(CASE WHEN LOCATION='Storage' THEN QTY ELSE 0 END), 
    SUM(CASE WHEN LOCATION='Active'  THEN QTY ELSE 0 END)
 FROM       
    ITEMS
 GROUP BY   
    ITEMDESC
 HAVING
    MAX(CASE WHEN LOCATION='Storage' THEN 1 ELSE 0 END) > 0