mysql - 添加到查询以获得以前结果 SQL 的总和

Question

我目前有一个提供以下结果集的查询，我现在需要添加到此查询以提供所有销售额底部的总计。我不确定该怎么做。

当前查询:

SELECT
    product,
    COUNT(OrderNumber) AS CountOf
FROM
    orders
WHERE
    STATUS = 'booking' AND
    Date(OrderDate) <= CURDATE() AND 
    Date(OrderDate) > DATE_SUB(CURDATE(),INTERVAL 30 DAY)

GROUP BY
    product
ORDER BY CountOf DESC

当前结果集:

product| count
-----------------------
pd1  | 3
pd4  | 1
pd2  | 1

期望的结果集 =

product| count
-----------------------
pd1  | 3
pd4  | 1
pd2  | 1
Total | 5

Answer 1

也许您可以添加一个 UNION 和一个包含总金额的 SELECT。像这样:

SELECT
    product,
    COUNT(OrderNumber) AS CountOf
FROM
    orders
WHERE
    STATUS = 'booking' AND
    Date(OrderDate) <= CURDATE() AND 
    Date(OrderDate) > DATE_SUB(CURDATE(),INTERVAL 30 DAY)
GROUP BY
    product
UNION
SELECT 'Total', count(OrderNumber) AS CountOf
FROM   orders
WHERE
    STATUS = 'booking' AND
    Date(OrderDate) <= CURDATE() AND 
    Date(OrderDate) > DATE_SUB(CURDATE(),INTERVAL 30 DAY)
ORDER BY CountOf DESC;