如何在同一SQL查询中计算分组求和占总计的百分比

我有一条按不同类别分组汇总金额的SQL查询，希望同时计算每组求和金额占总计的百分比。当前查询语句如下：

SELECT category.name, SUM(account.amount_default_currency) FROM account
INNER JOIN accounts ON account.accounts_id = accounts.id
INNER JOIN category ON account.category_id = category.id
INNER JOIN category_type ON category.category_type_id = category_type.id
GROUP BY category.name;

执行后得到结果：

请问该如何实现需求？

方法一：使用窗口函数（推荐，支持MySQL 8.0+、PostgreSQL、SQL Server等现代数据库） #

利用窗口函数SUM() OVER()直接计算全局总计，无需额外子查询：

SELECT 
    category.name,
    SUM(account.amount_default_currency) AS total_amount,
    ROUND(
        SUM(account.amount_default_currency) / SUM(SUM(account.amount_default_currency)) OVER() * 100,
        2
    ) AS percentage
FROM account
INNER JOIN accounts ON account.accounts_id = accounts.id
INNER JOIN category ON account.category_id = category.id
INNER JOIN category_type ON category.category_type_id = category_type.id
GROUP BY category.name;

• 内层SUM(account.amount_default_currency)是每组的金额汇总，外层SUM() OVER()计算所有分组的总和（全局总计）
• ROUND(..., 2)用于将百分比保留两位小数，可根据需求调整位数

方法二：子查询计算总计（适配不支持窗口函数的旧版数据库） #

先通过子查询算出符合过滤条件的总金额，再关联计算百分比：

SELECT 
    c.name,
    SUM(a.amount_default_currency) AS total_amount,
    ROUND(
        SUM(a.amount_default_currency) / total.total_amount * 100,
        2
    ) AS percentage
FROM account a
INNER JOIN accounts ac ON a.accounts_id = ac.id
INNER JOIN category c ON a.category_id = c.id
INNER JOIN category_type ct ON c.category_type_id = ct.id
CROSS JOIN (
    SELECT SUM(amount_default_currency) AS total_amount
    FROM account
    INNER JOIN accounts ON account.accounts_id = accounts.id
    INNER JOIN category ON account.category_id = category.id
    INNER JOIN category_type ON category.category_type_id = category_type.id
) AS total
GROUP BY c.name, total.total_amount;

• 子查询完全复用原查询的JOIN条件，确保总计和分组统计的数据集一致，避免结果偏差
• 使用CROSS JOIN将总计值关联到每一行分组数据中

内容的提问来源于stack exchange，提问作者Jose Sanche