How many orders are shipped by each method in the entire dataset?

basic_sql_explore_7.sql

select count(*)
from purchasing.purchaseorderdetail;
-- 8845

select count(distinct purchaseorderid)
from purchasing.purchaseorderdetail;
-- 4012

select count(*)
from purchasing.purchaseorderheader;
-- 4012

select count(distinct purchaseorderid)
from purchasing.purchaseorderheader;
-- 4012
-- each row of purchase order header has exactly 1 purchase order
-- There are the same number of purchase orders in purchaseorderheader as there
-- are in purchaseorderdetail

-- Join purchase order header to shipmethod
select *
from purchasing.purchaseorderheader x
join purchasing.shipmethod y
on x.shipmethodid = y.shipmethodid
limit 10;

-- Frequently when joining tables the number of records resulting is not what
-- you might have expected. In this case we are fine, but this can be a useful
-- check in order to avoid problems down the road.
select count(*)
from purchasing.purchaseorderheader x
join purchasing.shipmethod y
on x.shipmethodid = y.shipmethodid;


-- prefixing with x & y is not necessary except for the on clause
-- but it always it good to include so that you know where the columns are
-- coming from later on
-- furthermore we don't need to count distinct (just count) on "purchaseorderid"
-- & on large datasets count distinct may cause the query to run very slowly
-- however in some cases it may help avoid bugs (arising from misunderstanding
-- of the data, incorrect logic, or unexpected duplicates)
select y.name, count(distinct x.purchaseorderid) as num_orders
from purchasing.purchaseorderheader x
join purchasing.shipmethod y
on x.shipmethodid = y.shipmethodid
group by y.name
order by num_orders desc;


-- How many orders are purchased from each vendor?
-- &
-- What was the cost of the orders?
-- Return the results sorted by greatest cost descending
select
y.name,
count(distinct x.purchaseorderid) as num_orders,
sum(x.subtotal) as subtotal
from purchasing.purchaseorderheader x
left join purchasing.vendor y
on x.vendorid = y.businessentityid
group by y.name
order by subtotal desc;