クラシックファイルタイプは2021年1月をもって使用できなくなりました。クラシックファイルを移行することも、PDFをダウンロードすることもできます。詳細はこちら

SQL running total

回答済み
0

コメント

5件のコメント

  • Andrew McKenzie

    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. 

    1
  • Isabel Messore

    Hello 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, fruit

    Result:

    1
  • Peter Wetheridge

    Andrew 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!

    0
  • Andrew McKenzie

    I'm glad Isabel Messore's solution works for you Peter Wetheridge.

     

    0
  • Isabel Messore

    Peter Wetheridge You are most welcome - feel free to reach out with any other questions in the future, we're happy to help!

    0

サインインしてコメントを残してください。