Search code examples
sqlms-access

Create new table with conditional sums in Access SQL

enter image description here

I have a table which includes multiple articles and their respective stock data. In this example, Article x has 3 entries because it has 3 different status in column 'Status'. I would like to create a new table with the columns 'Article', 'Stock' and 'Locked Stock'. Each article should only have one entry. 'Stock' should be the sum of all the article entries where 'Status' = null or blank. 'Locked stock' should be the sum of all the article entries where 'Status' = not null or not blank. Please see the attached picture with the current table above and the desired table below. It makes the explanation easier to understand.

I tried to write the SQL query, but it doesn't give me any result yet:

SELECT Article,
       Sum(Select Table.Stock FROM Table WHERE Table.Stock Is Null) AS Stock,
       Sum(SELECT Table.Stock FROM Table WHERE Table.Stock Is Not Null) AS [Locked Stock]
FROM Table
GROUP BY Article];

Appreciate your help!

Kind regards, Marco

Solution

  • You seem to want conditional aggregation:

    select article,
       sum(iif(status is null, stock, 0)) as stock,
       sum(iif(status not null, stock, 0)) as locked_stock
from table.stock
group by article;

    To be honest, I don't know if you have more precise rules for determining locked stock. This just checks if the value is NULL or not NULL.