MySQL query for YTD Running Total

user02485

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:

 

date	store	Channel	Visits	YTD Total
1/1/2019	A	Banner	100	100
1/1/2019	A	Search	50	50
1/1/2019	A	Email	25	25
2/1/2019	A	Banner	75	175
2/1/2019	A	Search	55	105
2/1/2019	A	Email	30	55
3/1/2019	A	Banner	200	375
3/1/2019	A	Search	85	190
3/1/2019	A	Email	75	130
1/1/2019	B	Banner	500	500
1/1/2019	B	Search	100	100
1/1/2019	B	Email	65	65
2/1/2019	B	Banner	300	800
2/1/2019	B	Search	400	500
2/1/2019	B	Email	25	90
3/1/2019	B	Banner	250	1050
3/1/2019	B	Search	200	700
3/1/2019	B	Email	70	160

 

 

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! 

MarkSnodgrass

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

 

Unknown

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

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

jaeW_at_Onyx

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