mysql - SQL 按字段将总和数据连接在一起

Question

我正在尝试进行一个查询，获取总订单、所有订单的总价并按用户名对它们进行分组。

我有以下查询:

SELECT 
    `customers`.`name`          AS 'name',
    count(`orders`.`id`)        AS 'total orders',
    sum(`orderDetails`.`price`) AS 'total price'

FROM `customers`

INNER JOIN `orders`
ON 
    `orders`.`customer_id`      = `customers`.`id`

INNER JOIN `orderDetails`
ON
    `orderDetails`.`order_id`   = `orders`.`id`

WHERE 
    `customers`.`company_id`    = 1

GROUP BY
    `orders`.`id`,    -- Removing this will have the users grouped correctly, 
                      -- but display an invalid count.
    `customers`.`id`

目前我得到的结果是这样的:

'John', '2', '2.0000'
'Bill', '3', '3.0000'
'Bill', '1', '1.0000'
'John', '2', '2.0000'
'John', '3', '3.0000'

现实中:

John has 3 orders with a total price of 7.00
Bill has 2 orders with a total price of 4.00

有什么办法可以解决这个问题吗？

Answer 1

这里的问题是在不同级别进行分组。但是，在这种情况下，您可以使用 count(distinct

来修复它

select
    `customers`.`name` as 'name',
    count(distinct `orders`.`id`) as 'total orders',
    sum(`orderDetails`.`price`) as 'total price'
from
    `customers`
        inner join
    `orders`
        on `orders`.`customer_id` = `customers`.`id`
        inner join
    `orderDetails`
        on `orderDetails`.`order_id` = `orders`.`id`
where
    `customers`.`company_id` = 1
group by
   `customers`.`id`,
   `customers`.`name`