MySQL query for YTD Running Total

Hi all,

I'm having trouble creating a mySQL query that does a running total by a certain Group By dimensions.  

 

Here is a sample structure of the data:

 

datestoreChannelVisitsYTD Total
1/1/2019ABanner100100
1/1/2019ASearch5050
1/1/2019AEmail2525
2/1/2019ABanner75175
2/1/2019ASearch55105
2/1/2019AEmail3055
3/1/2019ABanner200375
3/1/2019ASearch85190
3/1/2019AEmail75130
1/1/2019BBanner500500
1/1/2019BSearch100100
1/1/2019BEmail6565
2/1/2019BBanner300800
2/1/2019BSearch400500
2/1/2019BEmail2590
3/1/2019BBanner2501050
3/1/2019BSearch200700
3/1/2019BEmail70160

 

 

The YTD Total column is what I'm trying to calculate.  The raw data is essentially the first 4 columns.  I need to create a Running Total of visits for the year by STORE and CHANNEL.  

 

Appreciate anyone's help!! Thanks! 

Comments

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    We have the Redshift SQL enabled in our instance and are able to use this SQL line to calculate a running total:

    sum(paymentamount) over(partition by ClaimNumber order by dateofeventyear,all_monthsofdev rows unbounded preceding) 

    I'm not seeing this exact syntax available available in MySQL, so maybe you can get Redshift enabled if it isn't already. 

    Are the Running Total cards which would eliminate the need to do any SQL not working for you?

    https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Building_Each_Chart_Type/Running_Total_Grouped_Bar_Graph

     




    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Thanks for your response. No I don't have Redshift, nor have the option to enable it.  I want to do a pre-calculation due to some cards that require it (such as the gauge cards).  Otherwise, I would just use the Running Total cards.  

     

    If anyone else has any ideas, much appreciated.  Thanks! 

  • GrantSmith
    GrantSmith Indiana 🥷

    @user06643 - You can utilize windowing functions within a beast mode calculation. For example:

     

    sum(`visits`) over(partition by `channel`, `store` order by `date`) 

     

     

    MySQL has supported windowing functions since version 8.0 so this should work in MySQL as well if you're at or above that version. If not then you'd need to utilize variables within MySQL to keep track and then reset the values when your store or channel changes which is quite a bit more complex.



    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    Domo does not have MySQL v8, so you actually won't find support for windowed functions in MySQL dataflows :(

     

    You can do it using variables

    Basic examples

    https://stackoverflow.com/questions/2563918/create-a-cumulative-sum-column-in-mysql

    With Partitions.

    https://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

     

     

    THIS IS THE FAR EASIER SOLUTION!

    Slight tweak to @GrantSmith 's beast mode sample.

    sum(sum(`visits`)) over(partition by `channel`, `store` order by `date`) 

    I didn't expect to reshare this video so many times, but you can do what you want to do via windowed functions in Beaast Modes.

    https://www.youtube.com/watch?v=ZPf41Fjn1H8

     

     


    Jae Wilson
    Check out my Domo Training YouTube Channel

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"