sum doesn't work while I use left join and group by

Question

I have two table: imports and orders: I have attached these respectively.

I want the followings: 1. sum amount of same product_id in imports table 2. sum of pieces of same product_id in orders table as status wise.

my query is:

SELECT `Import`.*, 
    SUM( case when orders.status = "sold" THEN orders.pieces else 0 end) as total_sell,
    SUM( case when orders.status = "No contact" THEN orders.pieces else 0 end) as no_contact,
    SUM( case when orders.status = "confirmed" THEN orders.pieces else 0 end) as confirmed,
    SUM( case when orders.status = "canceled" THEN orders.pieces else 0 end) as canceled
FROM `amrajegeachi`.`imports` AS `Import`
LEFT JOIN `orders`
    ON `Import`.`product_id` = `orders`.`product_id`
WHERE 1 = 1
GROUP BY `Import`.`id`

and result for this query:

Array
(
    [0] => Array
        (
            [Import] => Array
                (
                    [id] => 1
                    [category_id] => 2
                    [product_id] => 2
                    [amount] => 50
                    [cost] => 8320
                    [comment] => transportation and others cost: 100  
                    [created] => 2015-06-23 19:21:10
                )

            [0] => Array
                (
                    [total_sell] => 10
                    [no_contact] => 1
                    [confirmed] => 2
                    [canceled] => 0
                )

        )

    [1] => Array
        (
            [Import] => Array
                (
                    [id] => 2
                    [category_id] => 2
                    [product_id] => 2
                    [amount] => 15
                    [cost] => 3000
                    [comment] => 
                    [created] => 2015-06-22 18:10:36
                )

            [0] => Array
                (
                    [total_sell] => 10
                    [no_contact] => 1
                    [confirmed] => 2
                    [canceled] => 0
                )

        )

    [2] => Array
        (
            [Import] => Array
                (
                    [id] => 3
                    [category_id] => 2
                    [product_id] => 1
                    [amount] => 15
                    [cost] => 2000
                    [comment] => 
                    [created] => 2015-06-23 19:20:15
                )

            [0] => Array
                (
                    [total_sell] => 10
                    [no_contact] => 0
                    [confirmed] => 0
                    [canceled] => 0
                )

        )

)

My expected result is:

Array
(
    [0] => Array
        (
            [Import] => Array
                (
                    [id] => 1
                    [category_id] => 2
                    [product_id] => 2
                    [amount] => 65
                    [cost] => 8320
                    [comment] => transportation and others cost: 100  
                    [created] => 2015-06-23 19:21:10
                )

            [0] => Array
                (
                    [total_sell] => 10
                    [no_contact] => 1
                    [confirmed] => 2
                    [canceled] => 0
                )

        )


    [2] => Array
        (
            [Import] => Array
                (
                    [id] => 3
                    [category_id] => 2
                    [product_id] => 1
                    [amount] => 15
                    [cost] => 2000
                    [comment] => 
                    [created] => 2015-06-23 19:20:15
                )

            [0] => Array
                (
                    [total_sell] => 10
                    [no_contact] => 0
                    [confirmed] => 0
                    [canceled] => 0
                )

        )

)

How can I do this? I tried in different way such as:

SELECT `Import`.*, SUM(`Import`.`amount`) as total_import,
    SUM( case when orders.status = "sold" THEN orders.pieces else 0 end) as total_sell,
    SUM( case when orders.status = "No contact" THEN orders.pieces else 0 end) as no_contact,
    SUM( case when orders.status = "confirmed" THEN orders.pieces else 0 end) as confirmed,
    SUM( case when orders.status = "canceled" THEN orders.pieces else 0 end) as canceled
FROM `amrajegeachi`.`imports` AS `Import`
LEFT JOIN `orders`
    ON `Import`.`product_id` = `orders`.`product_id`
WHERE 1 = 1
GROUP BY `Import`.`id`

But no luck :'(

Answer 1

SELECT `Import`.*, SUM(`Import`.`amount`) as total_import,
    SUM( case when orders.status = "sold" THEN orders.pieces else 0 end) as total_sell,
    SUM( case when orders.status = "No contact" THEN orders.pieces else 0 end) as no_contact,
    SUM( case when orders.status = "confirmed" THEN orders.pieces else 0 end) as confirmed,
    SUM( case when orders.status = "canceled" THEN orders.pieces else 0 end) as canceled
FROM `amrajegeachi`.`imports` AS `Import`
LEFT JOIN `orders`
    ON `Import`.`product_id` = `orders`.`product_id`
WHERE 1 = 1
GROUP BY `Import`.`id

Answer 2

I am guessing that you want information about each product. If so, you can do this in a few ways. Here is a union all method:

SELECT product_id, sum(amount) as total_import,
       sum( case when status = 'sold' THEN pieces else 0 end) as total_sell,
       sum( case when status = 'No contact' THEN pieces else 0 end) as no_contact,
       sum( case when status = 'confirmed' THEN pieces else 0 end) as confirmed,
       sum( case when status = 'canceled' THEN pieces else 0 end) as canceled
from ((select i.product_id, amount, NULL as status, NULL as pieces
       from `amrajegeachi`.`imports` i
      ) union all
      (select o.product_id, NULL, o.status, o.pieces
       from `orders` o
      )
     ) io
group by product_id;