SQLite showing a percentage of a SUM from total SUM-CodePudding

I have a table with some expenses. I want to select the categories (not duplicated), the sum of expenses for each category, and what percentage means this sum from the total one.

For the first two things I succeeded, but for the total sum I didn't. I think because of GROUP BY.

SELECT SUM(Value), [Expense Category]
FROM expenses
WHERE Year = 2021
GROUP BY [Expense Category]
ORDER BY SUM(Value) DESC

Can I include the total sum in this SELECT? How?

Thank you!

CodePudding user response：

In this case you can use window funcion

SELECT DISTINCT SUM(Value) over(partition by [Expense Category])/SUM(Value), [Expense Category]
FROM expenses
WHERE Year = 2021
GROUP BY [Expense Category]

or just calculate sum in subquery

SELECT [Expense Category], sum_/total_sum
(
    SELECT SUM(Value) sum_, [Expense Category]
    FROM expenses
    WHERE Year = 2021
    GROUP BY [Expense Category]
    ORDER BY SUM(Value) DESC
) a 
CROSS JOIN (SELECT SUM(Value) total_sum FROM expenses) b

CodePudding user response：

You can use in your query the window function SUM() to calculate the total value of the table:

SELECT [Expense Category],
       SUM(Value) sum_value, 
       100.0 * SUM(Value) / SUM(SUM(Value)) OVER () percentage
FROM expenses
WHERE Year = 2021
GROUP BY [Expense Category]
ORDER BY SUM(Value) DESC;