Here is a screenshot of the queries from Access Northwind database.

1. Order Subtotals

For each order, calculate a subtotal for each Order (identified by OrderID). This is a simple query using GROUP BY to aggregate data for each order.

-- Get subtotal for each order.
select OrderID, 
    format(sum(UnitPrice * Quantity * (1 - Discount)), 2) as Subtotal
from order_details
group by OrderID
order by OrderID;

Here is the query result. 830 records returned.

2. Sales by Year

This query shows how to get the year part from Shipped_Date column. A subtotal is calculated by a sub-query for each order. The sub-query forms a table and then joined with the Orders table.

select distinct date(a.ShippedDate) as ShippedDate, 
    a.OrderID, 
    b.Subtotal, 
    year(a.ShippedDate) as Year
from Orders a 
inner join
(
    -- Get subtotal for each order
    select distinct OrderID, 
        format(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;

Here is the query result. 296 records returned.

3. Employee Sales by Country

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

select distinct b.*, a.CategoryName
from Categories a 
inner join Products b on a.CategoryID = b.CategoryID
where b.Discontinued = 'N'
order by b.ProductName;

Here is the query result. 296 records returned.

4. Alphabetical List of Products

This is a rather simple query to get an alphabetical list of products.

select distinct b.*, a.Category_Name
from Categories a 
inner join Products b on a.Category_ID = b.Category_ID
where b.Discontinued = 'N'
order by b.Product_Name;

Here is the query result. 69 records returned.

5. Current Product List

This is another simple query. No aggregation is used for summarizing data.

select ProductID, ProductName
from products
where Discontinued = 'N'
order by ProductName;

Here is the query result. 69 records returned.

Other tutorials in this category

1. MySQL Northwind Queries - Part 2

2. MySQL Northwind Queries - Part 3

3. How to Work with Two Unrelated Values

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