Suppose in Northwind database, we'd like to compare the number of customers versus the number of suppliers. Note that in this case, customers table and suppliers table don't have a common column for JOIN operation, but that won't cause any problems as in the SQL query, we can use the JOIN keyword without the ON keyword.

In the query below, we have two sub-queries in the FROM clause and each sub-query returns a single value. Because the results of the two sub-queries are basically temporary tables, we can join them like joining two real tables. In the SELECT clause, we simply list the two counts.

select a.CustomersCount, b.SuppliersCount
from
(select count(CustomerID) as CustomersCount from customers) as a
join
(select count(SupplierID) as SuppliersCount from suppliers) as b

Here is a screenshot of the query result.

The second query below uses the two values again but this time it calculates the ratio between customers count and suppliers count. The round and concat function are used to format the result.

select concat(round(a.CustomersCount / b.SuppliersCount), ":1") as Customer_vs_Supplier_Ratio
from
(select count(CustomerID) as CustomersCount from customers) as a
join
(select count(SupplierID) as SuppliersCount from suppliers) as b

Here is a screenshot of the query result.

Other tutorials in this category

1. MySQL Northwind Queries - Part 1

2. MySQL Northwind Queries - Part 2

3. MySQL Northwind Queries - Part 3

4. How to Fill Gaps in Sales Data

5. How to Calculate Totals, Subtotals and Grand Total

6. How to Work with NULL Values

7. How to fill down empty cells with values from a previous non-empty row

8. Use RANK function to update a previous record within a group or partition

9. Two ways to add a unique number or ID to each row

10. 3 ways to get Top N rows from MySQL

11. How to generate Cumulative Sum (running total) by MySQL - Part 1

12. How to generate Cumulative Sum (running total) by MySQL - Part 2

Back to Tutorial Index Page