Filter and perform multiple calculations-CodePudding

I have a dataset where I would like to group, filter and sum two different queries using SQL Server.

Data

name    stack   life    date        avail
aa      unused  remove  9/1/2021    5
aa      hi      keep    9/1/2021    1
aa      hey     stay    9/1/2021    1
aa      hey     fix     9/1/2021    2
aa      hey     keep    9/1/2021    4
aa      hi      no      9/1/2021    1
aa      hi      fix     9/1/2021    1
bb      ok      fix     9/1/2021    1
bb      yes     remove  9/1/2021    1
bb      ok      keep    9/1/2021    1
bb      ok      fix     9/1/2021    1
bb      ok      yes     9/1/2021    3
bb      unused  remove  9/1/2021    3
bb      ok      keep    10/1/2021   1

Desired (Logic)

Group by name and filter to the date of 9/1/2021
WHERE stack != 'unused'
AND WHERE life IN 'keep' and 'stay'

Group by name and filter to the date of 9/1/2021
WHERE stack != 'unused'
AND WHERE life IN 'keep'
Summing avail column

Output should be:

name   date            sum_avail
aa     9/1/2021        11
bb     9/1/2021        2

What I have tried:

SELECT name, date, SUM(avail) AS sum_avail   
FROM table
(WHERE stack NOT LIKE 'unused' AND WHERE life IN ('keep','stay') AND WHERE date == '9/1/2021')
 
(WHERE stack NOT LIKE 'unused' AND WHERE life IN ('keep') AND WHERE date == '9/1/2021')
GROUP BY name

Any suggestion is helpful.

CodePudding user response：

You can use select case statement to achieve this.

select [name], sum(case when life in ('keep','stay') then avail else 0 end)   
     sum(case when life in ('keep') then avail else 0 end)
from test
where date = '9/1/2021' and  stack!='unused'
group by [name]

see dbfiddle http://sqlfiddle.com/#!18/6895b/26

CodePudding user response：

SELECT name,date, SUM(avail) sum_avail FROM 
(SELECT * from Table_1 WHERE  stack != 'unused' AND life IN ('keep','stay') AND date='2021-09-01'
UNION ALL
SELECT * from Table_1 WHERE  stack != 'unused' AND life IN ('keep') AND date='2021-09-01') A
GROUP BY name, date

CodePudding user response：

Use single SUM() with multiple case expression as per logic. As search by single date then use MAX() for date but if date range search needed then use date column both select and group by portion.

-- SQL Server (v2017)
SELECT name, MAX(date) "date"
     , SUM(CASE WHEN life IN ('keep','stay') THEN avail ELSE 0 END
            
           CASE WHEN life = 'keep' THEN avail ELSE 0 END) sum_avail
FROM test_tbl
WHERE date = '9/1/2021'
    AND stack != 'unused'
GROUP BY name;

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=98f4dcee17dfe8c54edf7ab3e6fed561

CodePudding user response：

SELECT [name]
      ,[date]
      ,SUM(avail) as sum_avail FROM 
(SELECT * from Test WHERE life IN ('keep','stay')
UNION ALL
SELECT * from Test WHERE life IN ('keep') ) as Test1 
WHERE  stack != 'unused' AND date='2021-01-09'
GROUP BY [name],[date]