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

Question

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.

You have table name and column name reversed, so you'll get syntax errors (e.g. it must be `a.location`, not `location.a`. You are using a deprectaed join syntax. Since 1992 the SQL standard has explicit joins (`[INNER] JOIN` etc.). You don't need a join at all, anyway. — Thorsten Kettner, Jan 04 '23 at 21:08

Thorsten Kettner · Answer 1 · 2023-01-04T21:45:04.427

1

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;

edited Jan 04 '23 at 21:45

answered Jan 04 '23 at 21:04

Thorsten Kettner

89,309
7
49
73

This still shows *all* items in "Active" in addition to "Storage". I can only have Active items included if they are in the Storage location. basically, I want a list of Storage items, and then to see how much of that item is also in the active location. – Bagelwinner Jan 04 '23 at 21:34
That doesn't match the result you have shown. ItemD is in your result list, although it has no Storage row. You only excluded ItemC for neither being Active nor Storage. If you want Storage items only, you'll have to add a `HAVING` clause. – Thorsten Kettner Jan 04 '23 at 21:36
I see you have altered your request. I have updated my answer accordingly. The `WHERE` clause in that new query is no longer necessary, but may be able to make it run faster, as less rows have to be aggregated. – Thorsten Kettner Jan 04 '23 at 21:45
Thanks, this looks like it's getting closer, but I'm getting an error when I add that check to the HAVING clause: "Too many or too few operands specified for function" – Bagelwinner Jan 04 '23 at 22:12
Please check your code again. Maybe you've made a typing error. Here you can see that the query is working fine: https://dbfiddle.uk/q7h-h3p4 – Thorsten Kettner Jan 05 '23 at 07:38
Thank you for the help, it was definitely not a typo. The query ran fine (with the incorrect results) without the HAVING clause, then throws the error I mentioned as soon as that HAVING clause is added. It's a syntax error; their documentation is not great. The software I'm stuck with is, as you've seen with my use of deprecated syntax, very dated. I was able to get the results showing correctly with the insight of your solution, but now I'm stuck behind another wall in regards to duplication of results from joining another table because this software doesn't allow SELECTs in the FROM lol – Bagelwinner Jan 05 '23 at 18:14

score 1 · Answer 2 · edited Jan 04 '23 at 21:56

1

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;

edited Jan 04 '23 at 21:56

Thorsten Kettner

89,309
7
49
73

answered Jan 04 '23 at 21:47

Ethan

11
2

Yes, an `IN` clause is a very appropriate method to get the desired items. – Thorsten Kettner Jan 04 '23 at 21:55

Spaghetti · Answer 3 · 2023-01-04T21:14:50.060

0

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

edited Jan 04 '23 at 21:14

answered Jan 04 '23 at 21:07

Spaghetti

153
9

1

The OP wants to exclude items that have no storage or action rows. You think you know from the sample data that this is true only for ItemC, but is it the same case in the real environment.? Won't the table content ever change? Will you always select all data from the table, look at all rows and then amend your query to exclude the correct list of items? You are using the wrong type of quotes for the string literal by the way. It must be single quotes for a query that is standard compliant. – Thorsten Kettner Jan 04 '23 at 21:13
@ThorstenKettner 's answer is correct - fiddle reflects his response – Spaghetti Jan 04 '23 at 21:16

score 0 · Answer 4 · answered Jan 04 '23 at 21:37

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

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

4 Answers4

Update