CODEWITHSUNDEEP
sqlitegroup-bysumwindow-functions
Lucian Mardale
Lucian Mardale

Reputation: 23

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!

Upvotes: 2

Views: 393

Answers (2)

forpas
forpas

Reputation: 164139

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;

Upvotes: 1

KaySa
KaySa

Reputation: 71

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

Upvotes: 0

Related Questions