"OVER" function not there in SQL Dataflows - Running totals calculations

user03161
user03161 Member
in Archive

I am not able to perform "window" functions (specifically "over" function ) in a SQL Dataflow that I have created. Also - there were no dropdowns that suggest "OVER" exists as a function within Domo for SQL dataflows.

It is related to "Partition" and is used in Running Totals. Partition is there, but there is no "OVER" function available. I keep getting a syntax error in my dataflow because of this - I have tested out SQLFiddle and in other platforms where it works properly.

 

Very important function for calculating running totals as described here

SQL Fiddle - http://sqlfiddle.com/#!15/c9e0d/5

This is urgent and needs to be fixed asap

Comments

  • PodiumMason
    PodiumMason Contributor

    Hi there!

     

    MySQL does not support OVER() along with other window functions. 

     

    Redshift however, does support these. If you don't currently have access to Redshift dataflows, please contact Domo Support to have those turned on inside your Domo instance.

     

    This link may be helpful in emulating some windowed functionalities in mySQL: https://explainextended.com/2009/03/10/analytic-functions-first_value-last_value-lead-lag/

     

    Hope this is helpful.  

     

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • AS
    AS Coach

    Domo has a couple of SQL platforms.  Are you using mySQL?  

     

    We are on the other one, the RedShift platform, and use window functions pretty frequently.  For example, one of our revenue to goal calculations looks like:

    SELECT

    ...

    SUM(r.amount_usd) OVER (PARTITION BY r.type, r.salesrep_id, r.driver_category, DATE_TRUNC('yr',r.trx_month)) AS amount_yr_usd

    FROM

    ...

     

    You can have Domo turn on Redshift in your instance if you like.  I think you just have to contact your account or success manager.

     

    Let me know if you have other questions.

    Aaron
    MajorDomo @ Merit Medical

    **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"
  • DanHoughton
    DanHoughton Member

    Announced at domopalooza ‘18 is the support for window functions in beast mode. Sum(sum(x)) over () etc

Categories