SQL running total
BeantwoordMy query data includes fields 'year', 'period/month', 'category' and 'sales'.
I would like to create a field that gives me a running, YTD, total of 'sales', by 'period/month' and by 'category'.
How can I achieve this within the Workiva SQL platform?
e.g. from the data:
2023, 1, Fruit, $5
2023, 1, Veg, $6
2023, 2, Fruit, $4
The results would include:
2023, 1, Fruit, $5
2023, 2, Fruit, $9
-
Peter Wetheridge, although I believe you can do this in SQL (see Aggregate Functions — Presto 0.286 Documentation (prestodb.io) for a starting point), I'm guessing it may be easier to connect the query to a spreadsheet and then use a spreadsheet formula (such as SUMIF/SUMIFS) to achieve this.
1Hello Peter Wetheridge,
It's easiest to approach this problem using SQL, the sum() over partition is setup for this. I have tried this in my table using this SQL and it worked. Hope this helps!SELECT
"Test_RunningTotal"."year" AS "Year",
"Test_RunningTotal"."period" AS "Period",
"Test_RunningTotal"."fruit" AS "Fruit",
"Test_RunningTotal"."price" AS "price",
SUM(price) OVER (PARTITION BY Year, Fruit order by Year, Period ) AS running_total
FROM
"QWNjb3VudB8xMjA0OTA0MTgzMw"."c2799eda0b9648b0ba13a4b040398b8e" AS "Test_RunningTotal"
order by year,period, fruitResult:
1Andrew McKenzie - Andrew McKenzie - (apologies to one of you - there doesn't seem to be a way for me to see who is the right Andrew from the @-list) Thanks for the suggestion however, a spreadhseet would let me down when new rows are added - unless I pre-loaded rows with formulae and crossed fingers they didn't get exceeded.
Isabel Messore (hurrah - a unique name) - great - thank you! I have successfully mimicked the test you showed - how to apply it to my real-world data!0I'm glad Isabel Messore's solution works for you Peter Wetheridge.
0Peter Wetheridge You are most welcome - feel free to reach out with any other questions in the future, we're happy to help!
0U moet u aanmelden om een opmerking te plaatsen.
Opmerkingen
5 opmerkingen