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.

Answer 1

您尝试过ROLL UP运算符吗？似乎也有类似的问题：添加一个包含总数的摘要行

Answer 2

我很困惑。您已经在定义范围。如果你想要一个包含所有值的范围，只需将其添加到：

FROM line_item JOIn
product
USING (product_id) JOIN
( SELECT    0 lowest,  500 highest UNION ALL
SELECT  501   , 1000             UNION ALL
SELECT 1001   , 1500             UNION ALL
SELECT 1501   , 2000             UNION ALL
SELECT 2001   , 2500             UNION ALL
SELECT    0   , 2500             
-------^ all encompassing range
) ranges
ON product.price * line_item.quantity BETWEEN ranges.lowest AND ranges.highest

哇！这还有一个摘要行。