I have a table in snowflake which has the following format
| Year | Month | Sales |
|---|---|---|
| 2021 | 03 | a |
| 2021 | 03 | b |
| 2021 | 03 | c |
| 2021 | 04 | b |
| 2021 | 04 | c |
| 2021 | 04 | d |
| 2021 | 04 | f |
And I want to get the YTD count and the comma separated list of sales(ytd) So the output will be like
| Year | Month | Count | Sales |
|---|---|---|---|
| 2021 | 03 | 3 | a,b,c |
| 2021 | 04 | 7 | a,b,c,d,f |
I can get the YTD count but can't seem to figure out a way to get the list of sales. Any help will be appreciated
CodePudding user response：
I don’t have anywhere to test this at the moment but something like this should work:
WITH CTE1 AS (
SELECT DISTINCT YEAR, MONTH
FROM TABLE1
)
SELECT T1.YEAR, T1.MONTH,
COUNT(T2.SALES), LISTAGG(T2.SALES,’,’)
FROM CTE1 T1
INNER JOIN TABLE1 T2 ON T1.YEAR = T2.YEAR
AND T1.MONTH >= T2.MONTH
GROUP BY T1.YEAR, T1.MONTH