How to use subquery in SELECT statement in MySQL

Using subquery in SELECT statement

Use subquery in a SELECT statement when you need an aggregated value from the same table or from the another table.

Practice #1: Use subquery in SELECT statement with an aggregate function.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query lists the percentage of total units in 
stock for each product.
 
The subquery 
 
    select sum(UnitsInStock) from products
 
works out the total units in stock which is 3119.
 
Then 3119 is used in the outer query to calculate the
percentage of total units in stock for each product.
*/
select ProductID,
       ProductName,
       concat((UnitsInStock / (select sum(UnitsInStock) from products))*100, '%')
       as Percent_of_total_units_in_stock
from products
order by ProductID;
 
/*
This query returns the same result as query above.
Here 3119 is hardcoded whereas query above uses
subquery to calculate 3119 on the fly.
*/
select ProductID,
       ProductName,
       concat((UnitsInStock / 3119)*100, '%')
       as Percent_of_total_units_in_stock
from products
order by ProductID;

Query result set - 77 rows returned:
Use subquery in SELECT statement with an aggregate function.

Next tutorial: Using subquery in FROM clause
Back to Section Index: Using Subquery
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search