mysql - MySQL UNION ALL SELECT 关于获取每列的所有 SUM 的问题

Question

我希望得到每列产品的总和，不确定UNION ALL SELECT是否可以处理所需的结果，因为第一列是连接的。

这是代码：

                 SUM(product.product_id = 1) AS Soda,
                 SUM(product.product_id = 2) AS Liquor,
                 SUM(product.product_id = 3) AS Lemon,
                 SUM(product.product_id = 4) AS Mango,
                 SUM(product.product_id = 5) AS Inhaler,
                 SUM(1) AS Count

FROM line_item
JOIN product USING (product_id)
JOIN ( SELECT    0 lowest,  500 highest UNION
       SELECT  501   , 1000             UNION
       SELECT 1001   , 1500             UNION
       SELECT 1501   , 2000             UNION
       SELECT 2001   , 2500 ) ranges ON product.price * line_item.quantity BETWEEN ranges.lowest AND ranges.highest
GROUP BY ranges.lowest, ranges.highest

UNION ALL SELECT '','','','','','',
(
    SELECT 
    COUNT(product.price * line_item.quantity)
    FROM (line_item
    INNER JOIN product ON line_item.product_id = product.product_id)
);

**The output:**
+-------------+------+--------+-------+-------+---------+-------+
| Revenue     | Soda | Liquor | Lemon | Mango | Inhaler | Count |
+-------------+------+--------+-------+-------+---------+-------+
| 0 - 500     | 4    | 0      | 4     | 0     | 1       |     9 |
| 501 - 1000  | 0    | 0      | 0     | 2     | 0       |     2 |
| 1001 - 1500 | 0    | 1      | 0     | 2     | 2       |     5 |
| 1501 - 2000 | 0    | 2      | 0     | 0     | 1       |     3 |
| 2001 - 2500 | 0    | 1      | 0     | 0     | 0       |     1 |
|             |      |        |       |       |         |    20 |
+-------------+------+--------+-------+-------+---------+-------+

Thank for your help.

标签： mysqlsql

Answer 1

您是否尝试过 ROLL UP 运算符？似乎与您的问题类似：添加带有总计的摘要行