Custom Search
 


Using subquery in SELECT statement in MySQL


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.

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.





Other tutorials in this category

1. What is subquery

2. Using subquery to return a single value (known as single-value subquery or scalar subquery)

3. Using subquery to return a list of values (known as column subquery)

4. Using subquery to return one or more rows of values (known as row subquery)

5. Using correlated subqueries

6. Using EXISTS and NOT EXISTS in correlated subqueries in MySQL

7. Using subquery in FROM clause in MySQL

8. JOIN a table with a subquery

Back to Tutorial Index Page


Copyright © 2024 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy