Home > front end >  Sum a column into two different columns based on another column's value
Sum a column into two different columns based on another column's value

Time:01-05

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