Search code examples
sqlitegroup-bysumwindow-functions

SQLite showing a percentage of a SUM from total SUM

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!

Solution

  • 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;