MySQL query for YTD Running Total

Reply
Highlighted
Yellow Belt

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! 

Highlighted
Black Belt

Re: MySQL query for YTD Running Total

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...

 




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Highlighted
Yellow Belt

Re: MySQL query for YTD Running Total

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! 

Highlighted
Major Blue Belt

Re: MySQL query for YTD Running Total

@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 the heart icon**

**Did this solve your problem? Accept it as a solution!**
Highlighted
Red Belt

Re: MySQL query for YTD Running Total

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

 

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-numb...

 

 

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"
https://dojo.domo.com/t5/Beast-Mode-ETL-Dataflow/How-to-create-a-beast-mode-for-email-open-rate/m-p/47300#M8035 TLDR Grand Total over the entire dataset sum(...
Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.