-1

I have four tables: supplier, products, sales and sale_products. The suppliers and products table are associated in a one-to-many relationship, whereby a supplier can own multiple products and a product belongs to a single supplier. The products and sales tables are joined in a many-to-many relationship with the sale_products table acting as an intermediate table that connects the products and sales table.

Sample structure of the tables:

1. suppliers
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | Sup A              |
|  2 | Sup B              |
|  3 | Cus A              |
|  4 | Cus B              |
|  5 | Sky Song Fireworks |
+----+--------------------+

 2. products
+----+--------------------+-------------+
| id | name               | supplier_id |
+----+--------------------+-------------+
|  1 | Pop Pops           |           6 |
|  2 | Pop Pops           |           5 |
|  3 | Color Pop Pop Snap |           5 |
|  4 | Rubbing Bang       |           5 |
|  5 | Kids Bag           |           5 |
+----+--------------------+-------------+

 3. sales
+----+-----------------+-----------------------+
| id | discount_amount | receipt               |
+----+-----------------+-----------------------+
|  4 |            NULL | RCT-19-10-12-04/06/51 |
|  5 |            2000 | RCT-19-10-14-07/36/58 |
|  6 |            NULL | RCT-19-10-14-07/40/28 |
|  7 |            NULL | RCT-19-10-15-12/10/20 |
|  8 |            2000 | RCT-19-10-15-12/14/51 |
+----+-----------------+-----------------------+

4. sale_products
+----+------------+---------+----------+------------+
| id | product_id | sale_id | quantity | unit_price |
+----+------------+---------+----------+------------+
|  1 |        335 |       1 |        4 |        150 |
|  2 |        336 |       2 |        4 |       1000 |
|  3 |        335 |       3 |        5 |        150 |
|  4 |        445 |       4 |        5 |        150 |
|  5 |        564 |       5 |      100 |        200 |
+----+------------+---------+----------+------------+

I want to run a query that gets the total discount from sales table for each supplier whose product was part of a sale. The query could give a result as follows:

+----+--------------------+----------------+
| id | name               | total_discount |
+----+--------------------+----------------+
|  1 | Sup A              | 1000           |
|  2 | Sup B              | 200            |
+----+--------------------+----------------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Rodgers Wanyonyi
  • 1
  • Hi, have you tried anything yourself ? If yes, please show us. – VBoka Mar 04 '20 at 11:10
  • Your sample data is unusable and must be replaced with CREATE TABLE + INSERT INTO scripts. Your sample data is inconsistent. Your desired result does not match to the example data. You have not defined the algorithm of the result forming. You have not shown your attempt to solve your problem. – Akina Mar 04 '20 at 11:14

1 Answers1

0

If I understand correctly, then you just want a bunch of joins and aggregation:

select s.id, s.name, sum(discount_amount)
from suppliers s join
     products p
     on p.supplier_id = s.id join
     sale_products sp
     on sp.product_id = p.id join
     sales sa
     on sp.sale_id = sa.id
group by s.id, s.name;

You may want to add having sum(discount_amount) > 0 if you only want rows where the discount is not null.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for your suggestion @GordonLinoff. I do want a `join` and a `group by` query. However the result may not be accurate, assuming you have a sale with two different products belonging to same supplier hence the discount will be added twice instead of once. The discount in the sales table is not applied on each sold product but on the total cost of a sale. For this reason, the query has to aggregate the total discount from distinct sales per supplier. I'll appreciate your answer. – Rodgers Wanyonyi Mar 05 '20 at 09:20
  • @RodgersWanyonyi . . . I don't understand. The discount is per product within a sale, it is not per supplier. I don't see how they can be separated. – Gordon Linoff Mar 05 '20 at 11:14
  • That's the problem am facing, to get the disount per supplier. I just wanted to know if it can be achieved, and if so, what would be the possible query. – Rodgers Wanyonyi Mar 05 '20 at 14:05