MySQL计算同一列中的百分比

Question

在下表中，我如何计算每位客户的食品成本占总成本的百分比？希望将“食品成本”乘以 100，然后除以“总成本”。例如，客户 1 将返回 39.5。

+------------+------------+--------+
| customerId |  category  | total  |
+------------+------------+--------+
|          1 | Food Cost  |  65031 |
|          1 | Total Cost | 164637 |
|          2 | Food Cost  |  72031 |
|          2 | Total Cost | 264637 |
+------------+------------+--------+


SELECT round(total * 100 / (SELECT total FROM table where category = "Total 
Cost" and customerId = 1), 2) as Total FROM table where category = "Food 
Cost" and customerId = 1;

这将为我提供客户 1 所需的结果，但如果我想返回两个客户，我将需要删除 customerId=""，这将返回 MySQL 错误“错误代码:1242。子查询返回的值大于 1”行”。

Answer 1

您可以在此处使用条件聚合来获取每个客户的食品和总成本记录:

SELECT
    customerId,
    100.0 * MAX(CASE WHEN category = 'Food Cost' THEN total END) /
    MAX(CASE WHEN category = 'Total Cost' THEN total END) AS percentage
FROM yourTable
GROUP BY customerId;

Demo