MySQL – Get Quantity of Sold Products on a Specific Day

MySQL

I'm building a database for daily sales report, for example:

2 apples sold for 2.50 on 2021-02-12
1 apple sold for 2.00 on 2021-02-12
6 bananas sold for 3.50 on 2021-02-12
0 pears sold on 2021-02-12

Notice the "0 pears sold"

I have these tables:

Products

id (PK) name deleted_at
2 Apple null
5 Banana null
6 Pear null

Orders

id (PK) sale_date (DATE)
1 2021-02-12
2 2021-02-12

ProductOrders
|order_id (FK)|product_id (FK)|quantity|price|
|1 | 2 (it is "Apple") | 2 | 2.50 |
|1 | 5 (it is "Banana") | 3 | 3.50 |
|2 | 2 (it is "Apple") | 1 | 2.00 |
|2 | 5 (it is "Banana") | 3 | 3.50 |

I would like to get the products sold on 2021-02-12 grouped by price, keeping products that have not been sold so that I can write "0", as in the example.

I tried this query:

SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold,
    po.price AS price
FROM products p
    JOIN ProductOrders AS po ON p.id = po.product_id
    JOIN Orders AS o ON po.order_id  = o.id
WHERE o.sale_date = THE DATE
GROUP BY p.id, po.price
ORDER BY p.name, po.price

With this query I get:

2 apples sold for 2.50 on 2021-02-12
1 apple sold for 2.00 on 2021-02-12
6 bananas sold for 3.50 on 2021-02-12

Without the "0 pears sold", because the query discards the products that have not been ordered in "that date"

How can I to edit this query to get all products, also if they has not been sold in the date? Because I need to write 0 for the products has not been sold in "that date"

Thank you in advance and sorry for my bad english

EDIT

I forgot to say that I tried to use LEFT JOIN, but the problem is that in this way, it will keep all orders

SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold,
    po.price AS price
FROM products p
    LEFT JOIN ProductOrders AS po ON p.id = po.product_id
    LEFT JOIN Orders AS o ON po.order_id  = o.id AND o.sale_date = THE DATE
GROUP BY p.id, po.price
ORDER BY p.name, po.price

This query will keep all "ProductOrders" so it will returns many many rows, so I can't use this solution

SOLUTION

Thanks to the user nbk for this solution (I just added the WHERE clause to discard deleted products that have not orders on "THE DATE"):

SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    p.deleted_at,
    sum(po.quantity) AS quantity_sold,
    po.price as price
FROM products p
    LEFT JOIN (ProductOrders AS po 
        INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-12') AS o ON po.order_id = o.id
    ) ON p.id = po.product_id
WHERE (p.deleted_at IS NULL OR (p.deleted_at IS NOT NULL AND po.product_id IS NOT NULL))
GROUP BY p.id, p.name, p.deleted_at, po.price
ORDER BY p.name, po.price;

Best Answer

If you do a ddl you get quicker a good result.

you have to LEFT JOIN the tables to get the Rows with no partners.

Also add the where clause directly to the ON Clause or else you loose the gained rows.

CREATE TABLE products (
  `id` INTEGER Primary Key,
  `name` VARCHAR(6),
  `deleted_at` VARCHAR(4)
);

INSERT INTO products
  (`id`, `name`, `deleted_at`)
VALUES
  ('2', 'Apple', 'null'),
  ('5', 'Banana', 'null'),
  ('6', 'Pear', 'null');



CREATE TABLE Orders (
  `id` INTEGER PRIMARY KEY,
  `sale_date` DATE
);

INSERT INTO Orders
  (`id`, `sale_date`)
VALUES
  ('1', '2021-02-12'),
  ('2', '2021-02-12');

CREATE TABLE ProductOrders (
  `order_id` INTEGER,
  `product_id` VARCHAR(18),
  `quantity` INTEGER,
  `price` DECIMAL(10,2)
);

INSERT INTO ProductOrders
  (`order_id`, `product_id`, `quantity`, `price`)
VALUES
  ('1', '2', '2', '2.50'),
  ('1', '5', '3', '3.50'),
  ('2', '2', '1', '2.00'),
  ('2', '5', '3', '3.50');

SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold
FROM products p
    LEFT JOIN (ProductOrders AS po 
    INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-12') AS o ON po.order_id  = o.id)
    ON p.id = po.product_id
GROUP BY p.id, p.name,po.price
ORDER BY p.name;

prod_id | prod_name | quantity_sold
------: | :-------- | ------------:
      2 | Apple     |             1
      2 | Apple     |             2
      5 | Banana    |             6
      6 | Pear      |          null

SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(po.quantity) AS quantity_sold
FROM products p
    LEFT JOIN (ProductOrders AS po 
    INNER JOIN (SELECT * FROM Orders WHERE sale_date = '2021-02-13') AS o ON po.order_id  = o.id)
    ON p.id = po.product_id
GROUP BY p.id, p.name,po.price
ORDER BY p.name;

prod_id | prod_name | quantity_sold
------: | :-------- | ------------:
      2 | Apple     |          null
      5 | Banana    |          null
      6 | Pear      |          null

db<>fiddle here

Related Question