clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335534 fiddles created (27397 in the last week).

CREATE TABLE `products` ( `product_id` INT, `code` VARCHAR(24), `price` DECIMAL(10,2), `avgOrderQuantity` INT ); CREATE TABLE `orders` ( `order_id` INT, `date` DATETIME DEFAULT CURRENT_TIMESTAMP, `state` ENUM('pending','confirmed','cancelled','completed') ); CREATE TABLE `order_products` ( `order_id` INT, `product_id` INT, `quantity` INT, `price` DECIMAL(10,2) ); CREATE TABLE `invoices` ( `invoice_id` INT, `order_id` INT, `date` DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE `invoice_products` ( `invoice_id` INT, `product_id` INT, `quantity` INT, `price` DECIMAL(10,2) ); -- INSERT INTO `products` (`product_id`, `code`, `price`, `avgOrderQuantity`) VALUES (1, 'COD1', 12, 3), (2, 'COD2', 11, 1); INSERT INTO `orders` (`order_id`, `state`) VALUES (1, 'confirmed'), (2, 'confirmed'), (3, 'pending'); INSERT INTO `order_products` (`order_id`, `product_id`, `quantity`, `price`) VALUES (1, 1, 30, 10.2), (2, 1, 10, 12), (2, 2, 2, 11), (3, 1, 10, 12); INSERT INTO `invoices` (`invoice_id`, `order_id`) VALUES (1, 1), (2, 2), (3, 1); INSERT INTO `invoice_products` (`invoice_id`, `product_id`, `quantity`, `price`) VALUES (1, 1, 10, 10.2), (2, 1, 20, 12), (2, 2, 2, 11), (3, 1, 1, 10.2);
 hidden batch(es)


SELECT P.code, O.state, @ordered := OP.quantity AS ordered, @invoiced := IFNULL(( SELECT SUM(IP.quantity) FROM invoices AS I LEFT JOIN invoice_products AS IP ON IP.invoice_id = I.invoice_id WHERE I.order_id = O.order_id AND IP.product_id = OP.product_id ), 0) AS invoiced, @sold := IF(O.state = 'confirmed', @ordered, @invoiced) AS sold, @avgOrdQty := P.avgOrderQuantity AS avgOrdQty, IF(@sold > @avgOrdQty, @avgOrdQty, @sold) AS avgSold FROM order_products AS OP INNER JOIN orders AS O ON O.order_id = OP.order_id INNER JOIN products AS P ON P.product_id = OP.product_id WHERE O.state != 'pending';
code state ordered invoiced sold avgOrdQty avgSold
COD1 confirmed 30 11 30 3 3
COD1 confirmed 10 20 10 3 10
COD2 confirmed 2 2 2 1 1
 hidden batch(es)


SELECT T.code, T.sold, SUM(T.avgSold) FROM ( SELECT P.code, O.state, @ordered := OP.quantity AS ordered, @invoiced := IFNULL(( SELECT SUM(IP.quantity) FROM invoices AS I LEFT JOIN invoice_products AS IP ON IP.invoice_id = I.invoice_id WHERE I.order_id = O.order_id AND IP.product_id = OP.product_id ), 0) AS invoiced, @sold := IF(O.state = 'confirmed', @ordered, @invoiced) AS sold, @avgOrdQty := P.avgOrderQuantity AS avgOrdQty, IF(@sold > @avgOrdQty, @avgOrdQty, @sold) AS avgSold FROM order_products AS OP INNER JOIN orders AS O ON O.order_id = OP.order_id INNER JOIN products AS P ON P.product_id = OP.product_id WHERE O.state != 'pending' ) AS T GROUP BY T.code;
code sold SUM(T.avgSold)
COD1 30.00000000000000000000000000000000000000 6
COD2 2.00000000000000000000000000000000000000 1
 hidden batch(es)


SELECT P.code, O.state, OP.quantity AS ordered, IFNULL(( SELECT SUM(IP.quantity) FROM invoices AS I LEFT JOIN invoice_products AS IP ON IP.invoice_id = I.invoice_id WHERE I.order_id = O.order_id AND IP.product_id = OP.product_id ), 0) AS invoiced, IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold, P.avgOrderQuantity AS avgOrdQty, IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold FROM order_products AS OP INNER JOIN orders AS O ON O.order_id = OP.order_id INNER JOIN products AS P ON P.product_id = OP.product_id WHERE O.state != 'pending';
code state ordered invoiced sold avgOrdQty avgSold
COD1 confirmed 30 11 30 3 3
COD1 confirmed 10 20 10 3 3
COD2 confirmed 2 2 2 1 1
 hidden batch(es)


SELECT T.code, SUM(T.avgSold) FROM ( SELECT P.code, O.state, OP.quantity AS ordered, IFNULL(( SELECT SUM(IP.quantity) FROM invoices AS I LEFT JOIN invoice_products AS IP ON IP.invoice_id = I.invoice_id WHERE I.order_id = O.order_id AND IP.product_id = OP.product_id ), 0) AS invoiced, IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold, P.avgOrderQuantity AS avgOrdQty, IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold FROM order_products AS OP INNER JOIN orders AS O ON O.order_id = OP.order_id INNER JOIN products AS P ON P.product_id = OP.product_id WHERE O.state != 'pending' ) AS T GROUP BY T.code
code SUM(T.avgSold)
COD1
COD2
 hidden batch(es)


SELECT T.code, T.sold, SUM(T.avgSold) FROM ( SELECT P.code, O.state, OP.quantity AS ordered, IFNULL(( SELECT SUM(IP.quantity) FROM invoices AS I LEFT JOIN invoice_products AS IP ON IP.invoice_id = I.invoice_id WHERE I.order_id = O.order_id AND IP.product_id = OP.product_id ), 0) AS invoiced, IF(O.state = 'confirmed', (SELECT ordered), (SELECT invoiced)) AS sold, P.avgOrderQuantity AS avgOrdQty, IF((SELECT sold) > (SELECT avgOrdQty), (SELECT avgOrdQty), (SELECT sold)) AS avgSold FROM order_products AS OP INNER JOIN orders AS O ON O.order_id = OP.order_id INNER JOIN products AS P ON P.product_id = OP.product_id WHERE O.state != 'pending' ) AS T GROUP BY T.code
code sold SUM(T.avgSold)
COD1 30 6
COD2 2 1
 hidden batch(es)