Hello-
I have created a table ZZCEL_CUST_TRIAL as follows:
CREATE TABLE "RPT_MART"."ZZCEL_CUST_TRIAL"
(
"ORD_DAY_DW_ID" NUMBER,
"DAYPART_DW_ID" NUMBER,
"PRODUCT_DW_ID" NUMBER,
"COUNTS" NUMBER,
CONSTRAINT "PK_CUST_TRIAL" PRIMARY KEY ("ORD_DAY_DW_ID", "DAYPART_DW_ID", "PRODUCT_DW_ID")
)
The data in the table looks like this (sample of data)
ORD_DAY_DW_ID DAYPART_DW_ID PRODUCT_DW_ID COUNTS
5588 2 3316 6
5587 2 1323 3
5578 2 1323 3
5564 1 1323 8
5592 1 793 7
5580 1 3206 4
5592 1 1708 4
5589 2 3316 2
5566 1 1323 2
5603 3 3316 4
The counts column actually shows how many customers bought that product during that daypart on that particular day. Now I want to get a running count (cumulative) of customers by each daypart and each product so basically the output will look something like this:
Daypart_dw_id Product_dw_id RUNNING_COUNT
2 3316 8
3 3316 12
I'm trying to use this query to get the running count but the counts are not right ...
SELECT DISTINCT CTR.DAYPART_DW_ID,CTR.PRODUCT_DW_ID
,SUM(CTR.COUNTS) OVER (ORDER BY CTR.DAYPART_DW_ID,CTR.PRODUCT_DW_ID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) COUNTS
FROM rpt_mart.ZZCEL_CUST_TRIAL CTR
This is what I get which is obviously not correct. How can I get a running count of COUNTS by daypart and products. Thanks in advance for any help. Much appreciated!!
| DAYPART_DW_ID | PRODUCT_DW_ID | COUNTS |
| 1 | 2 | 95765 |
| 1 | 2 | 223140 |
| 1 | 2 | 338856 |
| 1 | 52 | 399870 |
| 1 | 52 | 399894 |
| 1 | 52 | 400087 |
| 1 | 52 | 400234 |