I have two queries I'd like to combine - and the json is kicking my butt.
The json extract below pulls a sku out of a lookup table along with the quantity ordered {"9999":2} It works fine.
If I add the 3 commented out portions, I get no results.
Then I'd like to combine it with this query, to get order counts as well as product qty ordered counts.
Don't have a whole lot of experience with the subqueries, so that and the json are what I am trying to surmount.
I'm sure there will be questions. I tend to always offer up MORE info than necessary, which tends to confuse people. When these orders are pulled from an external source, the skus that are being stored in the lookup tables are "kit" skus, when we process those orders the first thing we do, is explode the component skus into the base order lineitems (boli) table - so those skus in the lookup tables are only in the lookup tables, they don't get into the "boli" table, and the component skus break out into two or more individual skus requiring varying quantities to make up the kit. The qty ordered in the json in the base order lookups table applies to the KIT skus only, and is the qty I want associated with my calcuated order qty. If these were handled normally it would not be any issue. So hopefully that isn't too confusing to start out with.
any help would be greatly appreciated! TIA!
The json extract below pulls a sku out of a lookup table along with the quantity ordered {"9999":2} It works fine.
If I add the 3 commented out portions, I get no results.
SQL:
SELECT
`p`.`id`,
`p`.`sku`,
(SELECT
SUM(JSON_EXTRACT(`value`,
CONCAT('$.', JSON_QUOTE(`p`.`sku`))))
FROM
`base_order_lookups`
WHERE
`key` = 'original_skus') `total_qty_ordered`
FROM
`base_products` AS `p`
#join `base_order_lineitems` `boli` on `boli`.`product_sku` = `p`.`sku`
#join `base_orders` `bo` on `bo`.`id` = `boli`.`base_order_id`
WHERE
`p`.`type` = 'kit' #and date(`bo`.`order_date`) between '2019-03-27' and '2019-04-02'
Then I'd like to combine it with this query, to get order counts as well as product qty ordered counts.
SQL:
select
MAX(CASE
WHEN `key`= 'kit_sku' THEN `value`
END) 'Package',
count(`boli`.`base_order_id`) as 'Order_count', sum(`boli`.`final_sale_price` + `boli`.`shipping_charge`) as 'Order total less tax'
from `base_order_lineitems` `boli`
join `base_order_lineitem_lookups` `bolil` on bolil.base_order_lineitem_id=boli.id
join `base_orders` bo on `bo`.`id` = `boli`.`base_order_id`
where date(`bo`.`order_date`) between '2019-03-27' and '2019-04-30'
group by `bolil`.`value`
Don't have a whole lot of experience with the subqueries, so that and the json are what I am trying to surmount.
I'm sure there will be questions. I tend to always offer up MORE info than necessary, which tends to confuse people. When these orders are pulled from an external source, the skus that are being stored in the lookup tables are "kit" skus, when we process those orders the first thing we do, is explode the component skus into the base order lineitems (boli) table - so those skus in the lookup tables are only in the lookup tables, they don't get into the "boli" table, and the component skus break out into two or more individual skus requiring varying quantities to make up the kit. The qty ordered in the json in the base order lookups table applies to the KIT skus only, and is the qty I want associated with my calcuated order qty. If these were handled normally it would not be any issue. So hopefully that isn't too confusing to start out with.
any help would be greatly appreciated! TIA!