Running total for SQL column that is part of subquery

April 5, 2022

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?

>Solution :

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