Custom Search
 


Summarizing Data From Multiple Tables in MySQL


If you have followed the previous few tutorials in this category, you should have grasped solid knowledge about how to summarize data. Here we'll expand that knowledge to a new level - summarize aggregated data from multiple tables by using Group By clause and Join statement.

The purpose of using Join statement when aggregating data is to get extra information from other tables about the data you are summarizing. For example, if you need product sales for categories, you'll need to join with Categories table in Northwind.

Practice #1. Alphabetical List of Unique Products

This is a rather simple query to get a unique list of products (in alphabetical order) that have been discontinued and their relevant categories.

The first query uses the DISTINCT keyword to get a unique list of product names. The second query uses GROUP BY clause to get the same result as the first query but no DISTINCT keyword is required.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- Query 1:
select distinct b.ProductID, b.ProductName, a.CategoryName
from Categories a
inner join Products b on a.CategoryID = b.CategoryID
where b.Discontinued = 'y'
order by b.ProductName;

-- Query 2: this query gets the same result as Query 1
select b.ProductID, b.ProductName, a.CategoryName
from Categories a
inner join Products b on a.CategoryID = b.CategoryID
where b.Discontinued = 'y'
group by b.ProductID, b.ProductName, a.CategoryName
order by b.ProductName;

Here is the query result. 8 records returned.

Practice #2. Product sales with discount price

This query calculates sales for each product ordered after discount is applied. Product information is obtained from Products table, and the discount calculation is based on data in OrderDetails table. Then these two tables are joined to put together these combined information in the result set.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

-- Calculate product sales after discounts applied
select distinct y.OrderID,
y.ProductID,
x.ProductName,
y.UnitPrice,
y.Quantity,
y.Discount as Discount,
round(y.UnitPrice * y.Quantity * (1 - y.Discount), 2) as Sales_After_Discount
from Products x
inner join Order_Details y on x.ProductID = y.ProductID
order by y.OrderID;

Here is the query result. 2,155 records returned.

Practice #3. Number of Customers and Suppliers by City

This query shows how to use UNION ALL to merge Customers and Suppliers into one result set by identifying them as having different relationships to Northwind Traders - Customers or Suppliers, and how many for each relationship type.

Note that, it does not matter whether we use UNION or UNION ALL in this query because results from the two sub-queries don't have identical rows due to different values in Relationship Type column- either Customers or Suppliers.

To make the result easy to understand, in the result set screenshot, I highlighted the two rows for London - there are 6 customers and 1 supplier in London.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

select City, count(*) as "Number Count", 'Customers' as "Relationship Type"
from Customers
group by City
union all
select City, count(*) as "Number Count", 'Suppliers' as "Relationship Type"
from Suppliers
group by City
order by City;

Here is the query result. 98 records returned.

Practice #4. Number of units in stock by category and supplier continent

This query has two joins to retrieve product and category information into the result set. The first one joins with Products table to get the number of units in stock. The second one joins with Categories table to get category name.

Case statement is used in GROUP BY clause to list the number of units in stock for each supplier's continent. Note that, if only s.Country (not the entire case statement) is used in the GROUP BY clause, duplicated rows will exist for each product category and supplier continent.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

select c.CategoryName as "Product Category", 
case when s.Country in
('UK','Spain','Sweden','Germany','Norway',
'Denmark','Netherlands','Finland','Italy','France')
then 'Europe'
when s.Country in ('USA','Canada','Brazil')
then 'America'
else 'Asia-Pacific'
end as "Supplier Continent",
sum(p.UnitsInStock) as Units_In_Stock
from Suppliers s
inner join Products p on p.SupplierID=s.SupplierID
inner join Categories c on c.CategoryID=p.CategoryID
group by c.CategoryName,
case when s.Country in
('UK','Spain','Sweden','Germany','Norway',
'Denmark','Netherlands','Finland','Italy','France')
then 'Europe'
when s.Country in ('USA','Canada','Brazil')
then 'America'
else 'Asia-Pacific'
end;

Here is the query result. 21 records returned.





Other tutorials in this category

1. Using Aggregate Functions and Group By Clause in MySQL

2. More About GROUP BY Clause in MySQL

3. Using GROUP BY with HAVING Clause in MySQL

4. Summarizing Data From Multiple Tables in MySQL - Part 2

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