CODEWITHSUNDEEP
sqlsql-server
Angeline Tran
Angeline Tran

Reputation: 21

SQL Server 2008: How do you SUM based on two conditions?

If I work at a grocery store and need to make orders for inventory, we make orders multiple times a month rather than one large order.

Item ETA QTY
Apples 5/6/21 10
Apples 6/12/21 15
Apples 6/30/21 10
Bananas 6/12/21 15
Bananas 7/5/21 20
Cereal 5/15/21 10
Cereal 5/30/21 50
Cereal 7/15/21 20

Is there a way to create a table that sums the QTY, if the item is the same and if the ETA month is the same to know how much of each Item is expected to arrive in a given month?

Ideally, the result I'm looking for is something that looks like this

Item May June July
Apples 10 25 0
Bananas 0 15 20
Cereal 60 0 20

I would need the code to first check to see what month the item is expected to arrive in, and then if there are more than one lines that have the same item and ETA month, SUM the QTY.

I have tried doing CASE WHEN statements but always end up with syntax errors

SELECT 
    CASE WHEN ETA BETWEEN '2021-05-01' AND '2021-05-31'
    AND WHERE Item IN 
        (SELECT Item 
        FROM ['Inventory'] 
        GROUP BY Item HAVING COUNT(*)>1)
        THEN SUM(QTY)
    END AS MAY_QTY
        
FROM [dbo].['Inventory'];

Upvotes: 2

Views: 49

Answers (2)

Byron Alex
Byron Alex

Reputation: 82

First you should group the data by item and month, and then use pivot to convert rows to columns.

select
    item, 
    isnull(may,0) as May, 
    isnull(june,0) as June, 
    isnull(july,0) as July
from
(
  select item, datename(month, ETA) as _month, sum(qty) as qty
  from Inventory
  group by item, datename(month, ETA)
) d
pivot
(
  sum(qty)
  for _month in (may, june, july)
) piv;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You just use conditional aggregation:

select item,
       sum(case when month(eta) = 5 then qty else 0 end) as may,
       sum(case when month(eta) = 6 then qty else 0 end) as jun,
       sum(case when month(eta) = 7 then qty else 0 end) as jul
from inventory i
group by item;

I would caution you that using months without a year may lead to problems. That is also true of using unsupported software -- SQL Server 2008 is no longer supported.

Upvotes: 3

Related Questions