带有 GROUP BY 子句的 COUNT 的 MySQL SUM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14880080/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL SUM of a COUNT with GROUP BY clause
提问by Jeepstone
I wish to SUMthe COUNTof a query as follows. This query returns the count properly (1) for each row, but not sure how to add them all up.
我想SUM了COUNT一个查询，如下所示。此查询正确返回每行的计数 (1)，但不确定如何将它们全部加起来。
SELECT COUNT(*), jss_orders_headers.* FROM jss_orders_headers
LEFT JOIN jss_orders_extrafields
ON jss_orders_headers.orderID = jss_orders_extrafields.orderID
AND jss_orders_extrafields.extraFieldID = 5
GROUP BY jss_orders_headers.orderID
ORDER BY jss_orders_headers.orderID DESC
Table Structure is
表结构是
jss_order_headers
jss_order_headers
orderID, etc
订单 ID 等
jss_order_extrafields
jss_order_extrafields
exid, orderID, extrafieldID, extrafieldName, content
exid、orderID、extrafieldID、extrafieldName、内容
This currently returns data as follows:
当前返回的数据如下：
COUNT() | orderID | etc
计数() | 订单编号 | 等等
1 | 99
1 | 99
1 | 104
1 | 104
1 | 106
1 | 106
I need to return the SUMof the COUNT()column. So in the 3 examples above I would return 3.
我需要返回SUM的的COUNT()列。所以在上面的 3 个例子中，我会返回3.
Many thanks
非常感谢
回答by Taryn
Your question is not exactly clear but if you only want the sum()of all orders, then you should be able to use something like this:
你的问题不是很清楚，但如果你只想要sum()of all orders，那么你应该能够使用这样的东西：
select sum(TotalByOrder) TotalOrders
from
(
SELECT COUNT(*) TotalByOrder, jss_orders_headers.*
FROM jss_orders_headers
LEFT JOIN jss_orders_extrafields
ON jss_orders_headers.orderID = jss_orders_extrafields.orderID
AND jss_orders_extrafields.extraFieldID = 5
GROUP BY jss_orders_headers.orderID
) src
回答by Dave S.
Would WITH ROLLUPdo what you need?
会WITH ROLLUP做你需要的吗？
SELECT COUNT(*), jss_orders_headers.* FROM jss_orders_headers
LEFT JOIN jss_orders_extrafields
ON jss_orders_headers.orderID = jss_orders_extrafields.orderID
AND jss_orders_extrafields.extraFieldID = 5
GROUP BY jss_orders_headers.orderID DESC WITH ROLLUP
Why there's no ORDER BY?
为什么没有ORDER BY？
When you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. In other words, ROLLUP and ORDER BY are mutually exclusive. However, you still have some control over sort order. GROUP BY in MySQL sorts results, and you can use explicit ASC and DESC keywords with columns named in the GROUP BY list to specify sort order for individual columns.
使用 ROLLUP 时，也不能使用 ORDER BY 子句对结果进行排序。换句话说，ROLLUP 和 ORDER BY 是互斥的。但是，您仍然可以控制排序顺序。MySQL 中的 GROUP BY 对结果进行排序，您可以对 GROUP BY 列表中命名的列使用显式 ASC 和 DESC 关键字来指定各个列的排序顺序。
