如何在与 mysql 的同一查询中使用 SUM 和 COUNT 并获得准确的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4115038/
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
How can I use SUM and COUNT in the same query with mysql and get accurate results
提问by JungAtHeart
I have two tables in a one to many relationship. More specifically, t1 is order information and t2 is line item details on those orders.
我有两个表处于一对多关系中。更具体地说，t1 是订单信息，t2 是这些订单的行项目详细信息。
I'm trying to use a query like this:
我正在尝试使用这样的查询：
SELECT COUNT(DISTINCT(t1.id)) order_count,
SUM(t1.order_total) order_total,
SUM(t2.product_price) product_total,
DATE(t1.order_date) order_date
FROM t1
LEFT JOIN t2 ON t1.id = t2.id
GROUP BY t1.order_date
The query returns the correct value for order_count. However the other values are inflated incorrectly. I understand that with the left join I'm adding extra rows and that's why the sum's are incorrect. I'm just not sure how to fix it.
查询返回 order_count 的正确值。然而，其他值被错误地夸大了。我知道使用左连接我添加了额外的行，这就是总和不正确的原因。我只是不知道如何解决它。
Any help would be greatly appreciated.
任何帮助将不胜感激。
EDIT: The output should be something like this:
编辑：输出应该是这样的：
DATE | ORDER COUNT | GRAND TOTAL
日期 | 订单数 | 累计
I developed the query below based on a response. It returns all values correctly except for the coupon_total which it returns as 0 every time.
我根据响应开发了以下查询。它正确返回所有值，除了coupon_total 每次都返回0。
SELECT
COUNT(DISTINCT(o.order_number)) order_count,
DATE(o.order_date) order_date,
SUM(o.total_product_total) product_total,
SUM(o.total_shipping) shipping_total,
SUM(o.total_grand_total) grand_total,
o.coupon_total
FROM (
SELECT
DATE(o.order_date) order_date,
o.order_number,
o.total_product_total,
o.total_shipping,
o.total_grand_total,
IF(op.record_type='cpn',SUM(op.price),0) coupon_total
FROM orders o
LEFT JOIN orders_products op ON o.order_number=op.order_number
GROUP BY o.order_number
) o
GROUP BY DATE(o.order_date)
ORDER BY o.order_date DESC
回答by MartinStettner
I'd use a subquery to first sum over the values of the subtable like
我会使用子查询首先对子表的值求和，例如
SELECT t1.id, t1.order_total, SUM(t2.product_price) product_total
FROM t1 LEFT JOIN t2 on t1.id=t2.id
GROUP BY t1.id, t1.order_total
this query returns all single orders with their summed product price.
此查询返回所有单个订单及其总产品价格。
The make the outer query summing over order_totaland returning the count like
使外部查询求和order_total并返回计数
SELECT COUNT(DISTINCT(t1.id)) order_count,
SUM(t1.order_total) order_total,
SUM(t2.product_price) product_total
FROM (
SELECT t1.order_date, t1.id, t1.order_total, SUM(t2.product_price) product_total
FROM t1 LEFT JOIN t2 on t1.id=t2.id
GROUP BY t1.order_date, t1.id, t1.order_total
) GROUP BY t1.order_date
No garantuee that this code actually works, my SQL's a bit rusty... But I hope you got the idea.
不保证这段代码真的有效，我的 SQL 有点生疏……但我希望你能明白。
EDIT(in response to your edits...)
编辑（响应您的编辑...）
The IF-construct is misplaced in your code: Either you use something like SUM(IF(op.record_type='cpn',op.price,0))or, even better, place a WHEREclause in your inner query selecting only OPs with record_type='cpn', i.e. make it
该IF-construct是放错了地方在你的代码：要么你使用类似SUM(IF(op.record_type='cpn',op.price,0))或者甚至更好，放置一个WHERE子句中的内部查询选择只与有机磷record_type='cpn'，即使它
....
SUM(op.price) coupon_total
FROM orders o
LEFT JOIN orders_products op ON o.order_number=op.order_number
WHERE op.record_type='cpn'`
GROUP BY o.order_number
....
