By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Orders (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
order_number VARCHAR(255),
product_id INT,
Qty INT);
CREATE TABLE Products (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255),
UnitPrice DECIMAL(9,2));
INSERT INTO Products VALUES
(1,"product1",0.25),
(2,"product2",0.33),
(3,"product3",0.5),
(4,"product4",1),
(5,"product5",0.62),
(6,"product6",1),
(7,"product7",1.17),
(8,"product8",1),
(9,"product9",1.5),
(10,"product10",1.25),
(11,"product11",2.75),
(12,"product12",6),
(13,"product13",2.17),
(14,"product14",7),
(15,"product15",1.88),
(16,"product16",2.67),
(17,"product17",4.25),
(18,"product18",2.25),
(19,"product19",9.5),
(20,"product20",2.5);
Records: 20 Duplicates: 0 Warnings: 0
INSERT INTO Orders(order_number, product_id, Qty)
VALUES
('ORD0001', 1, 10), ('ORD0001', 2, 1), ('ORD0001', 3, 5), ('ORD0001', 4, 7),
('ORD0002', 1, 9),('ORD0002', 3, 2),('ORD0002', 4, 2),('ORD0003', 2, 3),
('ORD0003', 4, 4),('ORD0004', 1, 10),('ORD0004', 2, 11),('ORD0004', 3, 1),
('ORD0004', 4, 1),('ORD0004', 5, 1),('ORD0005', 5, 2),('ORD0005', 6, 8),
('ORD0006', 7, 2),('ORD0006', 8, 2),('ORD0006', 10, 5),('ORD0007', 9, 4),
('ORD0007', 11, 3),('ORD0008', 12, 13),('ORD0008', 19, 10),('ORD0008', 20, 10),
('ORD0009', 1, 10), ('ORD0009', 2, 1), ('ORD0010', 13, 5), ('ORD0010', 14, 7),
('ORD0011', 1, 9),('ORD0012', 3, 2),('ORD0012', 4, 2),('ORD0013', 2, 3),
('ORD0013', 4, 4),('ORD0014', 1, 10),('ORD0014', 2, 11),('ORD0014', 3, 1),
('ORD0014', 4, 1),('ORD0014', 15, 1),('ORD0015', 15, 2),('ORD0015', 16, 8),
('ORD0016', 17, 2),('ORD0016', 18, 2),('ORD0017', 10, 5),('ORD0017', 19, 4),
('ORD0018', 11, 3),('ORD0018', 12, 13),('ORD0019', 19, 10),('ORD0020', 20, 10);
Records: 48 Duplicates: 0 Warnings: 0
SELECT P.*
FROM Products P
JOIN ( SELECT product_id
FROM Orders
GROUP BY product_id
ORDER BY SUM(Qty) DESC
LIMIT 10) O
ON P.id=O.product_id;
| id | product_name | UnitPrice |
|---|---|---|
| 1 | product1 | 0.25 |
| 2 | product2 | 0.33 |
| 12 | product12 | 6.00 |
| 19 | product19 | 9.50 |
| 4 | product4 | 1.00 |
| 20 | product20 | 2.50 |
| 3 | product3 | 0.50 |
| 10 | product10 | 1.25 |
| 6 | product6 | 1.00 |
| 16 | product16 | 2.67 |
SELECT P.*
FROM Orders O
JOIN Products P ON O.product_id=P.id
GROUP BY product_id
ORDER BY SUM(Qty) DESC
LIMIT 10;
| id | product_name | UnitPrice |
|---|---|---|
| 1 | product1 | 0.25 |
| 2 | product2 | 0.33 |
| 12 | product12 | 6.00 |
| 19 | product19 | 9.50 |
| 4 | product4 | 1.00 |
| 20 | product20 | 2.50 |
| 3 | product3 | 0.50 |
| 10 | product10 | 1.25 |
| 6 | product6 | 1.00 |
| 16 | product16 | 2.67 |
SELECT P.*
FROM Orders O
JOIN Products P ON O.product_id=P.id
GROUP BY product_id
ORDER BY SUM(UnitPrice*Qty) DESC
LIMIT 10;
| id | product_name | UnitPrice |
|---|---|---|
| 19 | product19 | 9.50 |
| 12 | product12 | 6.00 |
| 20 | product20 | 2.50 |
| 14 | product14 | 7.00 |
| 16 | product16 | 2.67 |
| 4 | product4 | 1.00 |
| 11 | product11 | 2.75 |
| 1 | product1 | 0.25 |
| 10 | product10 | 1.25 |
| 13 | product13 | 2.17 |