Custom Search
 


MySQL Northwind Queries - Part 3


This is part 3 of the tutorial series - converting the popular Microsoft Access Northwind database to MySQL queries. These queries are originated from Access Northwind Traders application. Some of them are relatively complex aggregated queries with sub-queries.

11. Products Above Average Price

This query shows how to use sub-query to get a single value (average unit price) that can be used in the outer-query.

select distinct ProductName, UnitPrice
from Products
where UnitPrice > (select avg(UnitPrice) from Products)
order by UnitPrice;

Here is the query result. 25 records returned.

12. Product Sales for 1997

This query shows how to group categories and products by quarters and shows sales amount for each quarter.

select distinct a.CategoryName, 
b.ProductName,
format(sum(c.UnitPrice * c.Quantity * (1 - c.Discount)), 2) as ProductSales,
concat('Qtr ', quarter(d.ShippedDate)) as ShippedQuarter
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,
concat('Qtr ', quarter(d.ShippedDate))
order by a.CategoryName,
b.ProductName,
ShippedQuarter;

Here is the query result. 286 records returned.

13. Category Sales for 1997

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.

select CategoryName, format(sum(ProductSales), 2) as CategorySales
from
(
select distinct a.CategoryName,
b.ProductName,
format(sum(c.UnitPrice * c.Quantity * (1 - c.Discount)), 2) as ProductSales,
concat('Qtr ', quarter(d.ShippedDate)) as ShippedQuarter
from Categories as a
inner join Products as b on a.CategoryID = b.CategoryID
inner join Order_Details as c on b.ProductID = c.ProductID
inner join Orders as 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,
concat('Qtr ', quarter(d.ShippedDate))
order by a.CategoryName,
b.ProductName,
ShippedQuarter
) as x
group by CategoryName
order by CategoryName;

Here is the query result. 8 records returned.

14. Quarterly Orders by Product

This query shows how to convert order dates to the corresponding quarters. It also demonstrates how SUM function is used together with CASE statement to get sales for each quarter, where quarters are converted from OrderDate column.

select a.ProductName, 
d.CompanyName,
year(OrderDate) as OrderYear,
format(sum(case quarter(c.OrderDate) when '1'
then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 1",
format(sum(case quarter(c.OrderDate) when '2'
then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 2",
format(sum(case quarter(c.OrderDate) when '3'
then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 3",
format(sum(case quarter(c.OrderDate) when '4'
then b.UnitPrice*b.Quantity*(1-b.Discount) else 0 end), 0) "Qtr 4"
from Products a
inner join Order_Details b on a.ProductID = b.ProductID
inner join Orders c on c.OrderID = b.OrderID
inner join Customers d on d.CustomerID = c.CustomerID
where c.OrderDate between date('1997-01-01') and date('1997-12-31')
group by a.ProductName,
d.CompanyName,
year(OrderDate)
order by a.ProductName, d.CompanyName;

Here is the query result. 947 records returned.

15. Invoice

A simple query to get detailed information for each sale so that invoice can be issued.

select distinct b.ShipName, 
b.ShipAddress,
b.ShipCity,
b.ShipRegion,
b.ShipPostalCode,
b.ShipCountry,
b.CustomerID,
c.CompanyName,
c.Address,
c.City,
c.Region,
c.PostalCode,
c.Country,
concat(d.FirstName, ' ', d.LastName) as Salesperson,
b.OrderID,
b.OrderDate,
b.RequiredDate,
b.ShippedDate,
a.CompanyName,
e.ProductID,
f.ProductName,
e.UnitPrice,
e.Quantity,
e.Discount,
e.UnitPrice * e.Quantity * (1 - e.Discount) as ExtendedPrice,
b.Freight
from Shippers a
inner join Orders b on a.ShipperID = b.ShipVia
inner join Customers c on c.CustomerID = b.CustomerID
inner join Employees d on d.EmployeeID = b.EmployeeID
inner join Order_Details e on b.OrderID = e.OrderID
inner join Products f on f.ProductID = e.ProductID
order by b.ShipName;

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

16. Number of units in stock by category and supplier continent

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

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 UnitsInStock
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.

Here comes the end of this article series. I hope you find it useful in your day-to-day job of SQL coding! Don't forget to download the full script which can be found at the beginning of the first two parts of this article series.



Other tutorials in this category

1. MySQL Northwind Queries - Part 1

2. MySQL Northwind Queries - Part 2

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


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