ICE786 Blog We train the people
Assalam-o-Alaikum!
Dear friends today we will learn about INNER JOIN and GROUP BY. Sometimes you need to calculate some value that relates to another table or you need to pull some data from another table, for this you need Joining method. Here is a problem that we will solve.
Problem:
Suppose you have two tables, one is Items and second one is “sales”. There are some entries in sale table. Now you have to calculate total sale of every item and show in list along with Item name, but the problem is that there is no “Item Name” column in sale table.
Solution:
You have to use INNER JOIN and GROUP BY methods and SUM function of MySQL to solve this problem.
How?
Let’s see, you have following tables and data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
CREATE TABLE `items` ( `id` int(11) NOT NULL AUTO_INCREMENT, `item_name` varchar(255) DEFAULT NULL, `price` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; INSERT INTO `items` VALUES ('1', 'Item 1', '10'); INSERT INTO `items` VALUES ('2', 'Item 2', '20'); INSERT INTO `items` VALUES ('3', 'Item 3', '30'); INSERT INTO `items` VALUES ('4', 'Item 4', '40'); CREATE TABLE `sales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `item_id` int(11) DEFAULT NULL, `price` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1; INSERT INTO `sales` VALUES ('1', '1', '100'); INSERT INTO `sales` VALUES ('2', '1', '100'); INSERT INTO `sales` VALUES ('3', '1', '100'); INSERT INTO `sales` VALUES ('4', '2', '200'); INSERT INTO `sales` VALUES ('5', '2', '200'); INSERT INTO `sales` VALUES ('6', '3', '300'); INSERT INTO `sales` VALUES ('7', '3', '300'); INSERT INTO `sales` VALUES ('8', '4', '400'); INSERT INTO `sales` VALUES ('9', '4', '400'); |
You need this kind of result based on above table data:
| Id | Item Name | Total Sale |
| 1 | Item 1 | 300 |
| 2 | Item 2 | 400 |
| 3 | Item 3 | 600 |
| 4 | Item 4 | 800 |
To solve this problem use this query to get desired result:
|
1 2 3 4 5 6 7 8 9 |
SELECT items.id, items.item_name AS 'Item Name', SUM(sales.price)AS 'Total Sale' FROM items INNER JOIN sales ON items.id = sales.item_id GROUP BY item_id |
Now run the code and see the result.
Hope you enjoyed the tutorial. See you in next tutorial.
