Summarizing Data From Multiple Tables in MySQL

Summarizing Data From Multiple Tables in MySQL - Part 2

This is the second part of the tutorial about summarizing data from multiple Tables in MySQL. In this tutorial, we will see how aggregated data can sometimes come from a temporary table. The temporary table is actually a SELECT query that can be used with a JOIN statement or directly used after the FROM clause.

Practice #1: Join with a temporary table to get sales by year .

This query shows how to get a subtotal for each order. The subtotal is calculated by another query (also called sub-query) and the result of the sub-query is used as a temporary table in a JOIN statement where it is joined with the Orders table on OrderID column. Then the subtotal is retrieved from the sub-query for each order in SELECT statement.

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

select distinct a.ShippedDate, 
    a.OrderID, 
    b.Subtotal
from Orders a 
inner join
(
    -- Get subtotal for each order
    select distinct OrderID, 
        round(sum(UnitPrice * Quantity * (1 - Discount)), 2) as Subtotal
    from Order_Details
    group by OrderID    
) b on a.OrderID = b.OrderID
where a.ShippedDate is not null
    and a.ShippedDate between date('1996-12-24') and date('1997-09-30')
order by a.ShippedDate

Query result set - 296 rows returned:
Sales total.

Practice #2. Sales by Category

This query has two JOINs. Categories table joins with Products to get ProductName, and it also joins with a temporary table (as a result of a sub-query) to get Product Sales.

For each category, we get the list of products sold and the total sales amount. Note that, the inner query for table c is used to get sales for each product on each order. It then joins with outer query on Product_ID. In the outer query, products are grouped for each category.

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

select distinct a.CategoryID, 
    a.CategoryName, 
    b.ProductName, 
    round(sum(c.Extended_Price), 2) AS ProductSales
from Categories a 
inner join Products b on a.CategoryID = b.CategoryID
inner join 
(
    select distinct y.OrderID, 
        y.ProductID, 
        x.ProductName, 
        y.UnitPrice, 
        y.Quantity, 
        y.Discount, 
        y.UnitPrice * y.Quantity * (1 - y.Discount) as Extended_Price
    from Products x
    inner join Order_Details y on x.ProductID = y.ProductID
    order by y.OrderID
) c on c.ProductID = b.ProductID
inner join Orders d on d.OrderID = c.OrderID
where d.OrderDate between date('1997-01-01') 
                           and date('1997-12-30')
group by a.CategoryID, a.CategoryName, b.ProductName
order by a.CategoryName, b.ProductName, ProductSales

Here is the query result. 77 records returned.
Sales by Category in MySQL Northwind database.

Practice #3. Employee Sales by Country

This query is similar to the query above. It has two joins - a table and a temporary table.

For each employee, get their sales amount, broken down by country name.

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

select distinct a.Country, 
    a.LastName, 
    a.FirstName, 
    b.ShippedDate, 
    b.OrderID, 
    c.Subtotal as Sale_Amount
from Employees a
inner join Orders b on b.EmployeeID = a.EmployeeID
inner join 
(
    -- Get subtotal for each order
    select distinct OrderID, 
        round(sum(UnitPrice * Quantity * (1 - Discount)), 2) as Subtotal
    from Order_Details
    group by OrderID    
) c on b.OrderID = c.OrderID
where b.ShippedDate between date('1996-12-24') and date('1997-09-30')
order by a.LastName, a.FirstName, a.Country, b.ShippedDate

Here is the query result. 296 records returned.
MySQL Northwind - Employee Sales by Country.

Practice #4. Category Sales for 1997

This query doesn't use JOIN, but it has three joins in the sub-query (also is inner-query) after the FROM clause.

When you put a select statement into a FROM clause, it becomes a subquery. The subquery returns a temporary table in database server's memory and then it is used by the outer query for further processing.

This query shows sales figures by categories - mainly just aggregation with sub-query. The inner query aggregates to product level, and the outer query further aggregates the result set from inner-query to category level.

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

select CategoryName, round(Sum(ProductSales), 2) as Category_Sales
from
(
    select distinct a.CategoryName, 
        b.ProductName, 
        sum(c.UnitPrice * c.Quantity * (1 - c.Discount)) as ProductSales
    from Categories a
    inner join Products b on a.CategoryID = b.CategoryID
    inner join Order_Details c on b.ProductID = c.ProductID
    inner join Orders d on d.OrderID = c.OrderID 
    where d.ShippedDate between date('1997-01-01') and date('1997-12-31')
    group by a.CategoryName, 
        b.ProductName
    order by a.CategoryName, 
        b.ProductName
) as a
group by CategoryName
order by CategoryName

Here is the query result. 8 records returned.
Category Sales for 1997 by City in MySQL Northwind database.

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

Back to Tutorial Index Page

	Custom Search