SQL-计算筛选的SUM字段的百分比

Question

我有一个表，用于存储本地商店的销售额，按销售类型和年份划分，并按客户名称分组(只有几个客户(。用户通过JavaScript传递这些参数，我应该返回按销售类型分组的所选年份的所有销售。

当前的SQL语句返回这些年中由不同客户分组的属于第一种销售类型(即"一般"销售(的所有销售，但我也想检索所选销售的哪个百分比属于每个用户的金额。

表格：

CREATE TABLE `sales` (
`id_sale` INT(11) NOT NULL,
`name_of_client` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
`type_of_sale` TINYINT(1) NOT NULL,
`year` YEAR NOT NULL,
`amount` DOUBLE(22,0) NOT NULL,
PRIMARY KEY (`id_sale`) USING BTREE
)

SQL语句：

SELECT name_of_client, SUM(amount) 
FROM sales 
WHERE type_of_sale = 1 AND year = 2020 
GROUP BY name_of_client 
ORDER BY SUM(amount) DESC

我在SO线程中尝试了其他建议的解决方案，但他们计算了表中所有销售额的百分比，我需要每个客户在当前年份和当前销售类型中的SUM(金额(百分比。

Answer 1

如果我理解正确，这只是条件聚合：

SELECT name_of_client,
SUM(CASE WHEN type_of_sale = 1 THEN amount ELSE 0 END) / SUM(amount)
FROM sales
WHERE year = 2020
GROUP BY name_of_client
ORDER BY SUM(amount) DESC;

如果你每年都想要这个：

SELECT name_of_client, year,
SUM(CASE WHEN type_of_sale = 1 THEN amount ELSE 0 END) / SUM(amount)
FROM sales
GROUP BY name_of_client, year
ORDER BY SUM(amount) DESC