CODEWITHSUNDEEP
mysqlsql
riversxiao
riversxiao

Reputation: 369

group by consecutive period

Hi，I have a column as below

+--------+--------+
| day    |  amount| 
+--------+---------
| 2      |   2    |
| 1      |   3    | 
| 1      |   4    | 
| 2      |   2    |
| 3      |   3    | 
| 4      |   3    |
| 5      |   6    |
| 6      |   6    |
+--------+--------+

now I want something like this sum day 1- day3 as row one , sum day2-day4 as row 2, and so on.

+--------+--------+
| day    |  amount| 
+--------+---------
| 1-3    |   14   |
| 2-4    |   10   | 
| 3-5    |   12   |
| 4-6    |   15   |
+--------+--------+

Could you offer any one help ,thanks!

Upvotes: 1

Views: 87

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

I would just use a correlated subquery:

select day, day + 2 as end_day,
       (select sum(amount)
        from t t2
        where t2.day in (t.day, t.day + 1, t.day + 2)
       ) as amount
from (select distinct day from t) t;

This returns rows for all days, not limited to the last 4. If you really want that limit, then you can use:

select day, day + 2 as end_day,
       (select sum(amount)
        from t t2
        where t2.day in (t.day, t.day + 1, t.day + 2)
       ) as amount
from (select distinct day
      from t
      order by day
      offset 1 limit 99999999
     ) t
order by day;

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

You can use integer division in order to calculate 'days buckets' and group by each bucket:

SELECT (day - 1) DIV 3 AS bucket, SUM(amount) AS total
FROM mytable
GROUP BY (day - 1) DIV 3;

Output:

bucket  total
-------------
0       14
1       15

Demo here

To get the bucket string you can use:

SELECT concat(3 * ((day - 1) DIV 3 + 1) - 2, ' - ',
              3 * ((day - 1) DIV 3 + 1)) AS bucket, 
       SUM(amount) AS total
FROM mytable
GROUP BY (day - 1) DIV 3
order by day;

Output:

bucket  total
--------------
1 - 3   14
4 - 6   15

Note: The query works only for consecutive non-overlapping intervals.

Upvotes: 1

DineshDB
DineshDB

Reputation: 6193

Way 1:

Simply use UNION ALL:

SELECT '1 - 3' [Day], SUM(Amount)Amount FROM Your_Table WHERE Day BETWEEN 1 AND 3
UNION ALL
SELECT '2 - 4', SUM(Amount) FROM Your_Table WHERE Day BETWEEN 2 AND 4
UNION ALL
SELECT '3 - 5', SUM(Amount) FROM Your_Table WHERE Day BETWEEN 3 AND 5
UNION ALL
SELECT '4 - 6', SUM(Amount) FROM Your_Table WHERE Day BETWEEN 4 AND 6

Way 2:

You have to create a table with date range and JOIN the Table.

CREATE TABLE Tab1 (Day INT, Amount INT)
INSERT INTO Tab1 VALUES( 2 ,2 )   
 ,(1, 3)    
 ,(1, 4)    
 ,(2, 2)   
 ,(3, 3)    
 ,(4, 3)   
 ,(5, 6)   
 ,(6, 6)

CREATE TABLE Tab2 (DateRange VARCHAR(10), StartDate INT, EndDate INT)
INSERT INTO Tab2 VALUES ('1 - 3',1,3)
    ,('2 - 4',2,4)
    ,('3 - 5',3,5)
    ,('4 - 6',4,6)

SELECT T2.DateRange,SUM(T1.Amount) Amount
FROM Tab1 T1
JOIN Tab2 T2 ON T1.Day BETWEEN T2.StartDate AND T2.EndDate
GROUP BY T2.DateRange

OutPut:

Day     Amount
1 - 3   14
2 - 4   10
3 - 5   12
4 - 6   15

Upvotes: 1

Related Questions