CODEWITHSUNDEEP
sqlgroup-bypartitionsnowflake-cloud-data-platform
marcin2x4
marcin2x4

Reputation: 1429

Sum SQL rows on every nth and group

My data is as follows:

QUARTER_CMT   DEPT   SALES
7             A      39
8             A      23
9             A      33
10            A      45
11            A      50 
12            A      110
1             B      44
2             B      56
3             B      87
4             B      22

I am aiming at getting total SALES for every 4 quarters by DEPT and retain Quarter_CNT number as a rollup.

QUARTER_CMT   DEPT   SALES_TOT
7             A      140
11            A      160 
1             B      209

Work in progress code. I thought to sub-group totals and join to latest QUARTER_CMT to have one row by DEPT :

SELECT 
 ROW_NUMBER() OVER(PARTITION BY DEPTORDER BY QUARTER_CMT) as RN,
 QUARTER_CMT,
 DEPT,
--RANK() OVER(PARTITION BY ODDZIAL ORDER BY QUARTER_COUNT) AS RowNumberRankOrg,
--NTILE(4) OVER(PARTITION BY ODDZIAL ORDER BY QUARTER_COUNT) AS RowNumberRank,
SALES

FROM TABLE_1
ORDER BY DEPT, QUARTER_CMT

Upvotes: 2

Views: 1269

Answers (2)

waldente
waldente

Reputation: 1434

Try this:

with a as (
  select 
    row_number() over(partition by dept order by quarter_cmt)-1 n, 
    quarter_cmt, dept, sales
  from t
  order by dept, quarter_cmt
) 
select min(quarter_cmt) quarter_cmt, dept, sum(sales) sales_tot
from a
group by dept, floor(n/4) 
order by dept, quarter_cmt
;

Assuming this sample data:

create or replace table t as
select $1 QUARTER_CMT, $2 DEPT, $3 SALES from values 
  (7,'A',39),
  (8,'A',23),
  (9,'A',33),
  (10,'A',45),
  (11,'A',50),
  (12,'A',110),
  (1,'B',44),
  (2,'B',56),
  (3,'B',87),
  (4,'B',22)
;

Upvotes: 2

Serkan Arslan
Serkan Arslan

Reputation: 13393

you need to divide row_number to 5 for determine the periods

SELECT MIN (QUARTER_CMT) QUARTER_CMT, DEPT, SUM(SALES) SALES, PART
FROM (
    SELECT *, FLOOR(ROW_NUMBER() OVER(PARTITION BY DEPT ORDER BY QUARTER_CMT) / 5) AS PART
    FROM TABLE_1
) AS T
GROUP BY DEPT, PART
ORDER BY DEPT, PART

Result:

QUARTER_CMT DEPT SALES       PART
----------- ---- ----------- -----------
7           A    140         0
11          A    160         1
1           B    209         0

Upvotes: 1

Related Questions