Accumulated Sum: Subquery

JeanGuimaraes · January 31, 2021, 12:44am

Good night people. First of all, I apologize for my bad english.

I am studying SQL and training in graphics generation using MetaBase.
In one exercise you are asked to generate a billing graph per day, using accumulated sum.

Metabase has a built-in function that will do it via the GUI - called “Cumulative sum of …”, but I would like to add the graph later with respect to the current month and last month, this way I believe it is necessary to assemble the query through SQL command.

Only one detail is missing for my table to have an accumulated sum column. Could help me, please?

    SELECT temp1.Soma, temp1.Mês
FROM (SELECT SUM(T2.value) AS 'Soma', date(`T2`.`approved_at`) AS 'Mês'
        FROM ad_moip T2
        WHERE (str_to_date(concat(date_format(`T2`.`approved_at`, '%Y-%m'), '-01'), '%Y-%m-%d') = str_to_date(concat(date_format(now(), '%Y-%m'), '-01'), '%Y-%m-%d'))
            AND `T2`.`chargeback_at` IS null
        GROUP BY Mês
        ORDER BY Mês) AS temp1
GROUP BY temp1.Mês

flamber · January 31, 2021, 10:53am

Hi @JeanGuimaraes
Make the cumulative query in the sub-select, and in the parent-select you make a where-clause to only return between last and current month. Example:

...
WHERE str_to_date(concat(date_format(temp1.Mês, '%Y-%m'), '-01'), '%Y-%m-%d') BETWEEN str_to_date(concat(date_format(date_add(now(6), INTERVAL -1 month), '%Y-%m'), '-01'), '%Y-%m-%d')
   AND str_to_date(concat(date_format(now(6), '%Y-%m'), '-01'), '%Y-%m-%d')
GROUP BY temp1.Mês

You might find better help on a forum specific to your database, since your SQL isn't specific to Metabase - perhaps stackoverflow.com might be a good place.

You should be able to do it in the GUI, but there's currently an issue because of how cumulative calculations are implemented in Metabase:
Cumulative Sum/Count fails when there's an aggregation after the calculation · Issue #13634 · metabase/metabase · GitHub - upvote by clicking on the first post