Running total for SQL column that is part of subquery-CodePudding

Lots of posts here describe how to do running totals, but I'm faced with a situation where the running total needs to be that of a column that's calculated using a sub-query (which means my current ORDER BY causes the query to fail)

I have a table that show amounts per time period, something like this:

TimePeriod   Amount
2022-03-31   396
2022-03-31   16
2022-03-31   84
2021-12-31   842
2021-12-31   57
2021-09-30   652
2021-09-30   25
2021-09-30   173

In my query, I need to find the percentage of the total for each time period. What I've done is this:

SELECT 
    TimePeriod,
    SUM(Amount) AS 'Total Per Period', 
    CAST( ROUND( SUM(Amount)/(SELECT SUM(Amount) FROM MyDatabase.MyTable),3) AS DECIMAL(12,3)) AS 'Percentage of Total'
FROM
    MyDatabase.MyTable
GROUP BY
    TimePeriod
ORDER BY
    TimePeriod DESC

This gives me a correct output, like so:

TimePeriod   Total per Period   Percentage of total
2022-03-31   496                0.221
2021-12-31   899                0.400
2021-09-30   850                0.379

What I want to do is add a running total of the 'Percentage of total' column, something like:

TimePeriod   Total per Period   Percentage of total   Running total percentage
2022-03-31   496                0.221                 0.221
2021-12-31   899                0.400                 0.621
2021-09-30   850                0.379                 1.000

What I tried to do was first just add it in the first SELECT clause but that doesn't work since it's a column that only exists in my query. I then tried to do a select of that select, like so:

SELECT
    TimePeriod,
    'Total Per Period',
    'Percentage of Total',
    SUM('Percentage of Total') OVER (ORDER BY TimePeriod)
FROM
    (SELECT 
        TimePeriod,
        SUM(Amount) AS 'Total Per Period', 
        CAST( ROUND( SUM(Amount)/(SELECT SUM(Amount) FROM MyDatabase.MyTable),3) AS DECIMAL(12,3)) AS 'Percentage of Total'
    FROM
        MyDatabase.MyTable
    GROUP BY
        TimePeriod
    ORDER BY
        TimePeriod DESC)

This throws the error saying that the last ORDER BY is not allowed in sub-queries. Removing ORDER BY instead says that the syntax is incorrect. I'm guessing that the problem is that I have a subquery referencing a subquery result, but I'm not sure how to work around this one. What seems to be missing from my query?

CodePudding user response：

You might need to use [] to contain column name instead of ' which means string value, also we need to give subquery an alias name.

SELECT
    TimePeriod,
    [Total Per Period],
    [Percentage of Total],
    SUM([Percentage of Total]) OVER (ORDER BY TimePeriod)
FROM
    (SELECT 
        TimePeriod,
        SUM(Amount) AS 'Total Per Period', 
        CAST( ROUND( SUM(Amount)/(SELECT SUM(Amount) FROM MyDatabase.MyTable),3) AS DECIMAL(12,3)) AS 'Percentage of Total'
    FROM
        MyDatabase.MyTable
    GROUP BY
        TimePeriod) t1
ORDER BY
    TimePeriod DESC

CodePudding user response：

You have some syntax errors, as well as some improvements to make:

As mentioned in the other answer, the derived table needs an alias
Use [] to quote column names if necessary (preferably don't have such column names in the first place).
You cannot have ORDER BY inside a derived table or subquery, nor does it make sense to do so.
You can replace the SELECT SUM subquery with SUM(SUM) OVER () window function.
The running total window function must have ROWS UNBOUNDED PRECEDING if TimePeriod may have duplicates. It's also faster.
Because you are ordering by TimePeriod DESC, it may be faster to do the running total in the same order as the main ORDER BY but with ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING which works out to the same results. The benefit is one less sort in the query plan.

SELECT
    TimePeriod,
    [Total Per Period],
    [Percentage of Total],
    SUM([Percentage of Total]) OVER (ORDER BY TimePeriod DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM
    (SELECT 
        TimePeriod,
        SUM(Amount) AS [Total Per Period],
        CAST( ROUND( SUM(Amount) / SUM(SUM(Amount)) OVER () , 3) AS DECIMAL(12,3)) AS [Percentage of Total]
    FROM
        MyDatabase.MyTable
    GROUP BY
        TimePeriod
) t
ORDER BY
    TimePeriod DESC;

A further improvement would be to combine the whole thing into one level:

SELECT
    TimePeriod,
    SUM(Amount) AS [Total Per Period],
    CAST( ROUND(
            SUM(Amount) /
            SUM(SUM(Amount)) OVER ()
          , 3) AS DECIMAL(12,3)) AS [Percentage of Total],
    CAST( ROUND(
            SUM(SUM(Amount)) OVER (ORDER BY TimePeriod DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) /
            SUM(SUM(Amount)) OVER ()
          , 3) AS DECIMAL(12,3))
FROM
    MyDatabase.MyTable
GROUP BY
    TimePeriod
ORDER BY
    TimePeriod DESC;

Note that the results may be slightly different due to rounding.

SQL Fiddle

CodePudding user response：

This is my solution to your problem. I solve it in Oracle.

with flo as (
    select
      timeperiod, 
      sum(amount) as amount,
      cast((sum(amount) / (select sum(amount) from da ))as decimal(10,6)) *100.0 as percent_of_total
    from  yourtable
    group by timeperiod
)
select
  timeperiod,
  amount,
  percent_of_total,
  sum(percent_of_total) over (order by timeperiod desc ) as running
from flo;