SQL : How to aggregate values of one column depending on conditions applied on 2 other columns?

user_19701159 0
Viewed 72
      ID                COL1    COL2    DATE    DATE2       RES
      P4579841254       10      20  01/02/1900  01/04/1914  10
      P4579841254       20      25  01/03/1907  02/08/1918  57
      P4579841254       30      31  01/04/1914  03/12/1922  459
      P4579841254       70      71  01/05/1921  05/04/1927  7895
      P4579841254       70      71  01/06/1921  05/06/1927  2497
      P4579841254       71      20  01/06/1928  06/08/1931  1256
      P4579841254       20      75  01/07/1935  07/12/1935  325987

Hello Comm', I want to calculate the sum of the col[RES] by taking as following conditions:

COL1 >= 70 and DATE1 >= min(DATE1)

and

COL2 <= 75 and DATE2 <= max(DATE2)

with,

min(DATE1) taken when COL1=70

and

max(DATE2) taken when COL2=75

In other words I want : "if we have multiple COL1=70, we take only the one that gets the MIN(DATE) and for the COL2=75 we take the MAX(DATE) of all the rows showing COL2=75, once we have DTmin and DTmax we take all the values of RES column within this interval [DTmin;DTmax] and we sum"

For this ID, the results should be 335138 (sum of row4, row6 and row7) I tried something with following lines, but it becomes complex for me and my SQL level (for now) when I have to insert those SELECT in others upstream SELECT that enables grouping finally by ID :(

(SELECT "DT_MIN" FROM
(SELECT "ID",MIN("DATE1") as DT_MIN
FROM "MY_TABLE"
GROUP BY "ID","DATE1","COL1","COL2"
HAVING  ("COL1"='70')
)) as "DT_MIN_vf",

(SELECT "DT_MAX" FROM
(SELECT "ID",MAX("DATE2") as DT_MAX
FROM "MY_TABLE"
GROUP BY "ID","DATE2","COL1","COL2"
HAVING ("COL2"='75')
))as "DT_MAX_vf"

Need your help Specialists !

sql
1 Answers

This gives you your expected result, but I'm not sure I'm fully understanding the requirements. Please take a look at it and let me know if/what I'm misunderstanding.

WITH 
 min_d AS
  (SELECT min(m2.date1) d FROM mytable m2 WHERE m2.col1 = 70),
 max_d AS
  (SELECT max(m2.date2) d FROM mytable m2 WHERE m2.col2 = 75)
SELECT id, SUM(res)
FROM mytable
WHERE 
 date1 >= (SELECT d FROM min_d)
 AND (col1 <> 70 OR date1 = (SELECT d FROM min_d)) -- Handles the special case to only include the col1=70 row where date1=min_d, but unclear if it needs to be more general
 AND date2 <= (SELECT d FROM max_d)
GROUP BY id

The two CTEs at the top get the min and max dates you want. This is just to avoid duplicating them in the query, but there's no reason they have to be CTEs.

I have the special case for (col1 <> 70 OR ...) to ensure it only includes the col1=70 case where date1=min_d. I'm not at all confident I am understanding this rule in general, but this does give you the result you want.

You'll note that COL1 >= 70 and COL2 <= 75 do not appear anywhere here, despite you mentioning them in your question. You've said that the min(date1) should be calculated for col1=70 (not >=) and you've said that row 7 should be included despite COL1 being < 70, so I'm not sure where COL1 >= 70 and COL2 <= 75 is relevant.

user_55049220
Related