0

My problem is that I have following tables: packages, orders, order_items

order_items is halding id of package_id, order_id, and credits_number (which I need to retrieve)

order table can be with status = 1 (important for column credits_deducted_total) or 0 (important for credits_reserved_total)

credits_deducted_total and credits_reserved_total are giving me a problems because they should represent the number of deducted or reserved credits per project (which with following query is not the case).

Also 0 should be return as value for each project where I don't have orders with status = 0

Query which I was able to write:

select `packages`.`id` as `id`, `external_id`, `project_name`, `credits_available`, `credits_aq`, 
`price`, `currency_code`, `packages`.`status`, `packages`.`updated_at`, 
SUM(order_items.credits_number) as credits_deducted_total, 
SUM(order_items_reserved.credits_number) as credits_reserved_total from `packages` 
left join `order_items` on `order_items`.`package_id` = `packages`.`id` 
left join `order_items` as `order_items_reserved` on `order_items_reserved`.`package_id` = `packages`.`id` 
left join `orders` on `orders`.`id` = `order_items`.`order_id`
left join `orders` as `orders_reserved` on `orders_reserved`.`id` = `order_items_reserved`.`order_id`
where (`orders`.`status` = 1 or `orders_reserved`.`status` = 0)
group by `packages`.`id`;
0

1 Answer 1

0
select `packages`.`id` as `id`, 
       `external_id`, 
       `project_name`, 
       `credits_available`, 
       `credits_aq`, 
       `price`, 
       `currency_code`, 
       `packages`.`status`, 
       `packages`.`updated_at`, 
       SUM(order_items.credits_number) as credits_deducted_total, 
       SUM(order_items_reserved.credits_number) as credits_reserved_total 
from `packages` 
left join `order_items` 
     on `order_items`.`package_id` = `packages`.`id` 
left join `order_items` as `order_items_reserved` 
     on `order_items_reserved`.`package_id` = `packages`.`id` 
left join `orders` 
     on `orders`.`id` = `order_items`.`order_id`
    AND `orders`.`status` = 1
left join `orders` as `orders_reserved` 
     on `orders_reserved`.`id` = `order_items_reserved`.`order_id`
    AND `orders_reserved`.`status` = 0
group by `packages`.`id`;

Alternatively:

select `packages`.`id` as `id`, 
       `external_id`, 
       `project_name`, 
       `credits_available`, 
       `credits_aq`, 
       `price`, 
       `currency_code`, 
       `packages`.`status`, 
       `packages`.`updated_at`, 
       SUM(CASE WHEN `orders`.`status` = 1
                THEN order_items.credits_number
                ELSE 0
           END) as credits_deducted_total, 
       SUM(CASE WHEN `orders`.`status` = 0
                THEN order_items.credits_number
                ELSE 0
           END) as credits_reserved_total 
from `packages` 
left join `order_items` 
     on `order_items`.`package_id` = `packages`.`id` 
left join `orders` 
     on `orders`.`id` = `order_items`.`order_id`
group by `packages`.`id`;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.