CODEWITHSUNDEEP
mysqlsql
Paradoxis
Paradoxis

Reputation: 4708

SQL Join sum data together by field

I'm trying to make a query that gets the total orders, total price of all the orders combined and group them by a users name.

I have the following query:

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`

Currently I'm getting a result like such:

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

When in reality:

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

Is there any way I could fix this?

Upvotes: 0

Views: 42

Answers (2)

Laurence
Laurence

Reputation: 10976

The problem here is grouping at different levels. However, in this case you can fix it by using 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`

Upvotes: 1

Nick Heidke
Nick Heidke

Reputation: 2847

You'll only need one group by statement:

GROUP BY
`customers`.`name`

Upvotes: 0

Related Questions