I have sample data like below in my PieCloudDB Database:
| Date | Flow | Flag |
|---|---|---|
| 2024-05-20 | 583 | 1 |
| 2024-05-21 | 947 | 0 |
| 2024-05-22 | 216 | 1 |
| 2024-05-23 | 375 | 1 |
| 2024-05-24 | 492 | 1 |
| 2024-05-25 | 816 | 0 |
| 2024-05-26 | 753 | 0 |
| 2024-05-27 | 629 | 1 |
| 2024-05-28 | 104 | 1 |
| 2024-05-29 | 567 | 0 |
| 2024-05-30 | 128 | 1 |
I want to sum the Flow values based on the condition that Flag is 1, and the sum range is divided by Date(Merge consecutive dates), example output:
| Start | End | Sum |
|---|---|---|
| 2024-05-20 | 2024-05-20 | 583 |
| 2024-05-22 | 2024-05-24 | 1083 |
| 2024-05-27 | 2024-05-28 | 733 |
| 2024-05-30 | 2024-05-30 | 128 |
I tried this:
WITH CTE AS (
SELECT
date,
flow,
flag,
ROW_NUMBER() OVER (ORDER BY date) - ROW_NUMBER() OVER (PARTITION BY flag ORDER BY date) AS grp
FROM mytable
WHERE flag = 1
)
SELECT
MIN(date) AS start,
MAX(date) AS end,
SUM(flow)
FROM CTE
GROUP BY grp
ORDER BY start;
But I don't know why I get the output like this:
| Start | End | Sum |
|---|---|---|
| 2024-05-20 | 2024-05-30 | 2527 |
Try it like here:
WITH -- S a m p l e D a t a :
tbl_data ( a_date, flow, flag ) as
( Select To_Date('2024-05-20', 'yyyy-mm-dd'), 583, 1 From Dual Union All
Select To_Date('2024-05-21', 'yyyy-mm-dd'), 947, 0 From Dual Union All
Select To_Date('2024-05-22', 'yyyy-mm-dd'), 216, 1 From Dual Union All
Select To_Date('2024-05-23', 'yyyy-mm-dd'), 375, 1 From Dual Union All
Select To_Date('2024-05-24', 'yyyy-mm-dd'), 492, 1 From Dual Union All
Select To_Date('2024-05-25', 'yyyy-mm-dd'), 816, 0 From Dual Union All
Select To_Date('2024-05-26', 'yyyy-mm-dd'), 753, 0 From Dual Union All
Select To_Date('2024-05-27', 'yyyy-mm-dd'), 629, 1 From Dual Union All
Select To_Date('2024-05-28', 'yyyy-mm-dd'), 104, 1 From Dual Union All
Select To_Date('2024-05-29', 'yyyy-mm-dd'), 567, 0 From Dual Union All
Select To_Date('2024-05-30', 'yyyy-mm-dd'), 128, 1 From Dual
)
... create a grouping id (grp) for rows with flag = 1 using Count() Over() analytic function conditionaly as in the inner query below - then do the aggregations where flag = 1 grouped by grp column in the outer query ...
-- S Q L :
SELECT Min(a_date) as start_date, Max(a_date) as end_date, Sum(flow) as sum
FROM ( Select a_date, flow, flag,
Count(Case When flag = 0 Then 1 End) Over(Order By a_date) as grp
From tbl_data
)
WHERE flag = 1
GROUP BY grp
ORDER BY grp
/* R e s u l t :
START_DATE END_DATE SUM
---------- --------- -------
20-MAY-24 20-MAY-24 583
22-MAY-24 24-MAY-24 1083
27-MAY-24 28-MAY-24 733
30-MAY-24 30-MAY-24 128 */
See the fiddle here.
NOTE:
The answer is tested with Oracle but uses standard sql sintax. Sample data here is defined by a cte in WITH clause which you don't need. Just put your table name (or your cte name) instead of tbl_data in From clause of the SQL and make any other adjustment needed to fit your context (like column names ...).