CODEWITHSUNDEEP
sqlgroup-by
riversxiao
riversxiao

Reputation: 369

sql group by personalised condition

Hi，I have a column as below

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

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

+--------+--------+
| day    |  amount| 
+--------+---------
| 1-2    |   11   |
| 1-3    |   14   | 
| 1-4    |   17   |
+--------+--------+

Could you offer any one help ,thanks!

Upvotes: 1

Views: 39

Answers (2)

ShoeLace
ShoeLace

Reputation: 3576

with data as(
select 2 day, 2 amount from dual union all
select 1 day, 3 amount from dual union all
select 1 day, 4 amount from dual union all
select 2 day, 2 amount from dual union all
select 3 day, 3 amount from dual union all
select 4 day, 3 amount from dual)
select distinct day, sum(amount) over (order by day range unbounded preceding) cume_amount
from data
order by 1;

       DAY CUME_AMOUNT
---------- -----------
         1           7
         2          11
         3          14
         4          17

if you are using oracle you can do something like the above

Upvotes: 1

Udayraj Deshmukh
Udayraj Deshmukh

Reputation: 1914

Assuming the day range in left column always starts from "1-", What you need is a query doing cumulative sum on the grouped table(dayWiseSum below). Since it needs to be accessed twice I'd put it into a temporary table. 

CREATE TEMPORARY TABLE dayWiseSum AS 
(SELECT day,SUM(amount) AS amount FROM table1 GROUP BY day ORDER BY day);

SELECT CONCAT("1-",t1.day) as day, SUM(t2.amount) AS amount 
FROM dayWiseSum t1 INNER JOIN dayWiseSum
t2 ON t1.day > t2.day 
--change to >= if you want to include "1-1"
GROUP BY t1.day, t1.amount ORDER BY t1.day

DROP TABLE dayWiseSum;

Here's a fiddle to test with: http://sqlfiddle.com/#!9/c1656/1/0

Note: Since sqlfiddle isn't allowing CREATE statements, I've replaced dayWiseSum with it's query there. Also, I've used "Text to DDL" option to paste the exact text of the table from your question to generate the create table query :)

Upvotes: 0

Related Questions