2

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;

2 Answers 2

2

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

8
  • Thank you for the reply, I edited the post, because I forgot to say that I tried the LEFT JOIN, but it would be a problem because it will return all ProductOrders (many many rows). So it is not the solution
    – secon25
    Feb 20, 2021 at 19:53
  • Simpy reduce the number of joins by add also a on condition, you can only use left join to get the empty products. Please extebd your sample(my dbfiddle towith more rows that show wjat you want, as pears 0 orders was what you ask for
    – nbk
    Feb 20, 2021 at 20:05
  • How can I to reduce the number of joins? The problem with the LEFT JOIN is that it returns all the orders. For example, if you try the query and you just change AND o.sale_date = '2021-02-12' with AND o.sale_date = '2021-02-13' It would give the same results as "2021-02-12" that is wrong
    – secon25
    Feb 21, 2021 at 10:34
  • 1
    @secon25 i edited my answer, you can not reduce the joins as you need indormation from product and orders for your result, that information can only come from these tables.
    – nbk
    Feb 21, 2021 at 14:11
  • Thank you so much. It is working. Why did you put "p.name" in the group by? Is not p.id enough? Sorry for stupid question, as you can see I'm pretty noob, and I'm trying to undestand better the group by. One last thing, to discard old products that have been deleted (so the deleted_at column is not null) and these products have not orders in "THE DATE", should I put a condition in the WHERE clause? Because I tried to add WHERE p.deleted_at IS NULLbut it discards all the products that have been deleted, so if in "THE DATE" I sold a product, then it is deleted, I still have to see it
    – secon25
    Feb 21, 2021 at 16:14
1

When you don't specify the type of JOIN clause, by default MySQL assume you mean INNER JOIN which only returns records that match the predicate in your JOIN clause. Rather in your case you want all records from your products table so you should use an OUTER JOIN, specifically a LEFT OUTER JOIN, to return every record from the table to the left of the JOIN clauses, in this case the products table. You can actually use the shorthand notation of LEFT JOIN to mean the same thing. Here's an example with your query:

SELECT
    p.id AS prod_id,
    p.name AS prod_name,
    sum(coalesce(po.quantity, 0)) 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
WHERE o.sale_date = THE DATE 
    OR o.sale_date IS NULL
GROUP BY p.id, p.name, po.price
ORDER BY p.name, po.price

Notice I also added OR o.sale_date IS NULL to your WHERE clause to ensure we don't filter out the rows that don't match (i.e. the products that weren't sold on that date). When using an OUTER JOIN, the tables on the right side of the JOIN clause that don't have matching rows will have NULL values for their fields. We also then need to check if the po.quantity is NULL in the SUM() function as well so we can set it to 0 (our default value for when that product wasn't sold on that date).

Alternatively you can accomplish the same results with this query that leverages a UNION ALL with your original query in a CTE as well:

WITH CTE_ProductSales AS
(
    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, p.name, po.price
),
CTE_ProductSales_AllProducts AS
(
    -- Products that were sold
    SELECT prod_id, prod_name, quantity_sold, price
    FROM CTE_ProductSales

    UNION ALL

    -- Products that weren't sold
    SELECT id AS prod_id, name as prod_name, 0 AS quantity_sold, 0 as price
    FROM products P
    LEFT JOIN CTE_ProductSales PS
        ON P.prod_id = PS.prod_id
    WHERE PS.prod_id IS NULL -- Products that weren't sold on the date in the CTE
)

-- Final select of products sold with products that weren't sold
SELECT prod_id, prod_name, quantity_sold, price
FROM CTE_ProductSales_AllProducts 
ORDER BY prod_name, price

And finally here's a 3rd way you can accomplish this if you're running a version of MySQL that doesn't have CTEs:

-- Products sold on THE DATE
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

UNION ALL

-- Products that weren't sold on THE DATE
SELECT DISTINCT
    p.id AS prod_id,
    p.name AS prod_name,
    0 AS quantity_sold,
    0 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
WHERE o.sale_date = THE DATE
    AND po.product_id IS NULL
9
  • Thank you for the reply, I edited the post, because I forgot to say that I tried the LEFT JOIN, but it would be a problem because it will return all ProductOrders (many many rows). So it is not the solution
    – secon25
    Feb 20, 2021 at 19:53
  • @secon25 It shouldn't return any more records than one per each product and it's price it sold for on that date, because of you're grouping, no? Also notice my query is slightly different than the one you tried with a LEFT JOIN (and the other answers).
    – J.D.
    Feb 21, 2021 at 0:58
  • 1
    @secon25 Also if the first answer I provided still isn't sufficient, please see the alternative second answer I added which theoretically should be more efficient of a query (although more verbose).
    – J.D.
    Feb 21, 2021 at 4:15
  • 1
    @second Sorry haven't been able to test with the actual data, but I think that makes sense. Unfortunate you can't use CTEs, they're pretty helpful. But I added a 3rd way to accomplish what you're looking for.
    – J.D.
    Feb 21, 2021 at 14:00
  • 1
    @secon25 Sorry I forgot to add the DISTINCT keyword to my 3rd query. It should now produce the results you're looking for. Please try it again and let me know. Regarding your question on GROUP BY, when you use that clause you can only reference fields in your SELECT list that are listed in the GROUP BY unless you put them in an aggregate function (like MIN(), MAX(), COUNT(), etc). I believe some versions of MySQL actually let you directly reference a column not in the GROUP BY clause anyway, not sure why, but I think it returns randomish results. I meant to mention earlier.
    – J.D.
    Feb 21, 2021 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.