Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.45, 10.2.32, 10.3.23, 10.4.13, 10.5.4, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
-
Platforms:
- Windows 10 Pro - version 2004
- CloudLinux 7.8
Description
Use this schema as example:
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);
|
If we run this query:
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'; |
It returns:
| 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 |
Now If move this into a derived table to get the sumarized `avgSold` grouped by `code` like this
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 |
It returns:
| code | SUM(T.avgSold) |
|---|---|
| COD1 | null |
| COD2 | null |
This is NOT the expected result.
If I add a numeric column (YES it MUST be a numeric column) like this:
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 |
It returns:
| code | sold | SUM(T.avgSold) |
|---|---|---|
| COD1 | 30 | 6 |
| COD2 | 2 | 1 |
NOW this is the expected result
Attachments
Issue Links
- relates to
-
-
- Stalled
-
-
-
- Confirmed
-