CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Neethin Nandakumar
Neethin Nandakumar

Reputation: 49

How can I get the list of values from a snowflake table in Year to date format

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

Upvotes: 1

Views: 308

Answers (1)

NickW
NickW

Reputation: 9788

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

Upvotes: 1

Related Questions