/*
This query shows that, for each group of product 
categories, only one row of values is produced.
*/
select c.CategoryID,
       c.CategoryName,
       sum(UnitsInStock) as "Total units in stock"       
from products as p
inner join categories as c on c.CategoryID=p.CategoryID
group by p.CategoryID;

/*
This query is similar to Practice #1 but used a WHERE
clause to only select the first 5 product categories to
group. Note that WHERE clause is listed before GROUP BY
clause.
*/
select c.CategoryID,
       c.CategoryName,
       sum(UnitsInStock) as "Total units in stock"       
from products as p
inner join categories as c on c.CategoryID=p.CategoryID
where c.CategoryID in (1, 2, 3, 4, 5)
group by p.CategoryID;

/*
This query shows that, if the column used in GROUP BY 
clause contains null values, the null values are 
treated as a group.
 
There is one employee who does not need to report to anyone.
*/
select ReportsTo, count(*) as Num_of_employees
from employees
group by ReportsTo;

/*
This query shows that null values are removed by 
using is not null operator.
*/
select ReportsTo, count(*) as Num_of_employees
from employees
where ReportsTo is not null
group by ReportsTo;

/*
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 as s 
inner join Products as p on p.SupplierID=s.SupplierID
inner join Categories as 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;

/*
This query shows how to use case statement inside 
sum function to convert rows to columns.
 
Units in stock for each continent is retrieved and
each continent now has a column heading associated
with it.
 
If you compare with the result from Practice #4-2
above, you can see that rows in Supplier Continent
column have been converted to columns in this query's
result.
*/
select c.CategoryName as "Product Category", 
       sum(case when s.Country in 
                 ('UK','Spain','Sweden','Germany','Norway',
                  'Denmark','Netherlands','Finland','Italy','France')
                 then p.UnitsInStock end) as Europe,
       sum(case when s.Country in ('USA','Canada','Brazil') 
                 then p.UnitsInStock end) as America,
       sum(case when s.Country in ('Japan','Australia','Singapore')
                 then p.UnitsInStock end) as "Asia-Pacific"
from Suppliers as s 
inner join Products as p on p.SupplierID=s.SupplierID
inner join Categories as c on c.CategoryID=p.CategoryID
group by c.CategoryName;

/*
This query is the same as Practice #5-1 except that it used
IFNULL function to convert null values that are shown in 
Practice #5-1 to zero.
*/
select c.CategoryName as "Product Category", 
       ifnull(sum(case when s.Country in 
                 ('UK','Spain','Sweden','Germany','Norway',
                  'Denmark','Netherlands','Finland','Italy','France')
                 then p.UnitsInStock end), 0) as Europe,
       ifnull(sum(case when s.Country in ('USA','Canada','Brazil') 
                 then p.UnitsInStock end), 0) as America,
       ifnull(sum(case when s.Country in ('Japan','Australia','Singapore')
                 then p.UnitsInStock end), 0) as "Asia-Pacific"
from Suppliers as s 
inner join Products as p on p.SupplierID=s.SupplierID
inner join Categories as c on c.CategoryID=p.CategoryID
group by c.CategoryName;

/*
This query calculates the dollar amount sold for each product.
 
Column alias ShippedQuarter is used in GROUP BY clause. Most 
other database management systems (eg. Oracle, SQL Server) will
raise an error if you use column alias.
*/
select c.CategoryName, 
       p.ProductName, 
       concat('Qtr ', quarter(o.ShippedDate)) as ShippedQuarter,
       concat('$', round(sum(od.UnitPrice*od.Quantity*(1-od.Discount)), 2)) 
       as ProductSales
from orders as o 
inner join order_details as od on o.OrderID = od.OrderID
inner join products as p on od.ProductID = p.ProductID
inner join categories as c on c.CategoryID = p.CategoryID
where o.ShippedDate between '1996-5-1' and '1996-10-31'
group by c.CategoryName, p.ProductName, ShippedQuarter
order by ShippedQuarter, c.CategoryName, p.ProductName;

/*
This query is similar to the one above except that 
ShippedQuarter is not in GROUP BY clause. 
 
It's a surprise that MySQL server didn't raise an 
error but silently grouped the same products together
and used the first available quarter for the row. This
is a rather bad database behavior. For other DBMS 
systems (eg. Oracle, SQL Server), the ship quarter 
column must be used in GROUP BY clause or error will 
occur.
*/
select c.CategoryName, 
       p.ProductName, 
       concat('Qtr ', quarter(o.ShippedDate)) as ShippedQuarter,
       concat('$', round(sum(od.UnitPrice*od.Quantity*(1-od.Discount)), 2)) 
       as ProductSales
from orders as o 
inner join order_details as od on o.OrderID = od.OrderID
inner join products as p on od.ProductID = p.ProductID
inner join categories as c on c.CategoryID = p.CategoryID
where o.ShippedDate between '1996-5-1' and '1996-10-31'
group by c.CategoryName, p.ProductName
order by ShippedQuarter, c.CategoryName, p.ProductName;

/*
This query is modified from the query named as 
"Quarterly Orders by Product" in MS Access 
version of the Northwind database. It returns
the exact same result as it in Access.
*/
select p.ProductName, 
       c.CompanyName,
       year(o.OrderDate) as OrderYear,
       ifnull(round(sum(case when quarter(o.OrderDate)=1 
                        then od.UnitPrice*od.Quantity*(1-od.Discount) end), 2), 0) 
                        as Qtr1,
       ifnull(round(sum(case when quarter(o.OrderDate)=2 
                        then od.UnitPrice*od.Quantity*(1-od.Discount) end), 2), 0) 
                        as Qtr2,
       ifnull(round(sum(case when quarter(o.OrderDate)=3 
                        then od.UnitPrice*od.Quantity*(1-od.Discount) end), 2), 0) 
                        as Qtr3,
       ifnull(round(sum(case when quarter(o.OrderDate)=4
                        then od.UnitPrice*od.Quantity*(1-od.Discount) end), 2), 0) 
                        as Qtr4
from orders as o
inner join customers as c on c.CustomerID=o.CustomerID
inner join order_details as od on od.OrderID=o.OrderID
inner join Products as p on p.ProductID=od.ProductID
where o.OrderDate between '1997-1-1' and '1997-12-31'
group by p.ProductName, c.CustomerID, OrderYear;