inner-join-and-group-by

Our today’s topic is a little bit tricky. Sometimes you need to Get the SUM of Values from another Table to get the required data. This can also be done via PHP. But doing this with PHP is not a good practice. With SQL query you can save your time and extra processing. So let’s start our journey.

Table of Contants

  • Problem to the SUM of Values Matching Item ID from another Table
  • Solution for getting the required data
  • Explanation

Problem to Get the SUM of Values from another Table

Suppose you have two tables, one is Items and the second one is “sales”. There are some entries in the sales table.

Now you have to calculate or select the total sale of every item and show in the list along with Item name, but the problem is that there is no “Item Name” column in the sales table.

So what will we do? Is a simple query will work? I think No, then what? Before getting into deeper into the sea, let’s take a quick look at the below screenshot.

sales and items table for inner join
Sales and Items Tables

Solution for Getting Required Data via INNER JOIN

For better understanding, we will generate two tables. Therefore, we will create “items” and “sales” tables. I am giving you code for creating both tables with some dummy data as you saw in the screenshot. Copy and paste this code into Phpmyadmin’s Query box or any other software that can execute SQL queries. Code is here:

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 ('3', 'Item 3', '30');
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', '3', '100');
INSERT INTO `sales` VALUES ('3', '3', '100');
INSERT INTO `sales` VALUES ('4', '1', '200');
INSERT INTO `sales` VALUES ('5', '3', '200');

Our goal is to calculate the total sales of each item. For this purpose, we will use two keywords of the SQL query. One is INNER JOIN and the second is GROUP BY. With these two keywords, we can attempt our goals. Here is the query:

Now let’s try to explain these SQL queries.

Explanation of INNER JOIN

The first query is just for creating two tables “items & sales” and for some dummy data. In the second query block, we first, select data from items table with selecting id, item_name fields with using SUM function on the price field from the sales table. Next, we used INNER JOIN on the sales table by matching id from items table and item_id from sales table and then used group by item_id. I know this is difficult to understand at first glance but not too difficult. Do some homework and then try again to understand.

I tried to explain everything but if you need more help, then feel free to contact me HERE or just leave a message in the below box.

Here are some more cool stuff that will help you to understand SQL

Leave a Reply - I will show after approval