MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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);
  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


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


  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


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
null

COD2
null


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
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
code	sold	SUM(T.avgSold)
COD1	30.00000000000000000000000000000000000000	6
COD2	2.00000000000000000000000000000000000000	1