In the world of databases, we often need to perform the mathematical operations on the data that is stored in the tables. One such common operation is a mathematical division which is useful when we need to determine the values such as ratio, percentages, or any other derived metrics.
In this tutorial, we will learn about a common division operation that involves dividing two mathematical table columns.
Sample Table
For demonstration purposes, let us create a table that contains a metric data and use it to illustrate how to divide two columns in SQL.
id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
country_name VARCHAR(255) NOT NULL,
population INT NOT NULL,
distance FLOAT NOT NULL,
gdp DECIMAL(15,
2) NOT NULL DEFAULT(0)
);
This should create a table called “country_data” and contains a country information such as the country name, population, total distance, and gdp.
We can then insert the records into the table as follows:
INTO
country_data (country_name,
population,
distance,
gdp)
VALUES
('United States',
331002651,
9831.34,
22675248.00),
('China',
1439323776,
9824.58,
16642205.00),
('India',
1380004385,
3846.17,
2973191.00),
('Brazil',
212559417,
8326.19,
1839756.00),
('Russia',
145934462,
10925.55,
1683005.00);
The resulting output is as follows:
Divide Two Columns in SQL
Suppose we want to calculate the average population for every square units. We can divide the total population by the distance of the country.
To divide two columns in SQL, we use the “/” operator followed by the columns in which we wish to divide.
For example:
country_name,
population,
distance,
gdp,
(population / distance) AS avg_pop
FROM
country_data;
In this case, we divide the population column by the distance column and assign the resulting column with the “avg_pop” alias.
The resulting set is as follows:
This shows the average population of a country per square units.
Conclusion
In this tutorial, we learned how we can perform the mathematical division in SQL by dividing two table columns to fetch the results for each corresponding value.
