How to use GROUP BY with MAX or SUM depending on conditions?

1
votes

I am working on a SQL query with SQL Server (Microsoft SQL) that allows me to make different aggregations depending on a certain value. I want to do aggregations using MAX and SUM according to certain conditions (Moreover, I want to assume the maximum value but not exceeding a certain date (for example 2021/07/01)).

Let's take the following database:

Name Date Value
name1 2021-07-01 7
name1 2021-02-02 10
name1 2020-08-10 12
name1 2020-03-07 5
name2 2018-09-11 8
name2 2019-04-04 2
name2 2021-05-17 5
name3 2020-03-03 0
name3 2019-12-12 15
name3 2018-10-12 11
name4 2010-10-11 5
name4 2012-02-26 7

I want to aggregate the values associated to the names name1 and name2 using MAX applied on their dates (i.e. I want to get their value associated to the oldest date) and I want to aggregate the values associated to the names name3 and name4 using the SUM function on their associated values.

The result would be :

Name Value
name1 12
name2 5
name3 26
name4 12

I think using a CASE is a good idea but I haven't been able to implement it. I used the following post:

SQL: IF clause within WHERE clause

I tried the following, but I'm not sure on how to use it and when:

SELET Name, Value FROM Table
WHERE Value LIKE
    CASE WHEN Name in (name1, name2) THEN
        MAX(Date)
    ELSE
        Date <= '2021-07-01'
GROUP BY Name

Concerning the MAX not exceeding a certain value, I have no idea how to do it since MAX takes a column as parameter...

I am sure that this problem is not so difficult to solve when you master SQL, but on my side it is quite new!

I thank you for reading me.

2
sqlsql-server
Please mention which database you are using. – Jayadevan
@forpas just a mistake on my part, I edited the original post – jh0n37
@Jayadevan if you ask which database system I use, it's microsoft SQL, also I edited the original post – jh0n37

2 Answers

1
votes

You can use MAX() and SUM() window functions:

SELECT DISTINCT Name,
       CASE 
         WHEN Name IN ('name1', 'name2') THEN
           MAX(CASE WHEN Date <= '2021-07-01' THEN Value END) OVER (PARTITION BY Name)
         WHEN Name IN ('name3', 'name4') THEN
           SUM(Value) OVER (PARTITION BY Name)
       END Value    
FROM tablename

Or, conditional aggregation:

SELECT Name,
       CASE 
         WHEN Name IN ('name1', 'name2') THEN MAX(CASE WHEN Date <= '2021-07-01' THEN Value END)
         WHEN Name IN ('name3', 'name4') THEN SUM(Value)
       END Value    
FROM tablename
GROUP BY Name

See the demo.

0
votes

You can use conditional aggregation and some other conditional logic:

select name,
       coalesce(max(case when name in ('name1', 'name2') and max_date = date then value end),
                sum(value)
               )
from (select t.*,
             max(case when date <= '2021-07-01' then date end) over (partition by name) as max_date
      from t
     ) t
group by name;