我有一張桌子，上面有一些費用。我想選擇類別（不重復）、每個類別的費用總和，以及該總和與總費用的百分比。
前兩件事我成功了，但總和我沒有。我認為是因為 GROUP BY。
SELECT SUM(Value), [Expense Category]
FROM expenses
WHERE Year = 2021
GROUP BY [Expense Category]
ORDER BY SUM(Value) DESC
我可以在這個 SELECT 中包含總和嗎？如何？
謝謝！
uj5u.com熱心網友回復：
您可以在查詢中使用視窗函式SUM()來計算value表的總數：
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;
uj5u.com熱心網友回復：
在這種情況下，您可以使用視窗功能
SELECT DISTINCT SUM(Value) over(partition by [Expense Category])/SUM(Value), [Expense Category]
FROM expenses
WHERE Year = 2021
GROUP BY [Expense Category]
或者只是在子查詢中計算總和
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/375480.html
下一篇：sqlite選擇欄位時間的位置