Amount does not work while I use left join and group

Question

I have two tables: import and orders: I linked them accordingly.

I want the following: 1. the sum of the sum of the same product_id in the import table 2. the sum of the pieces of the same product_id in the order table as state.

my request:

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 the 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
                )

        )

)

Expected Result:

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 differently, for example:

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

I assume that you need information about each product. If so, you can do it in several ways. The following is the method union:

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;

Answer 2

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