Skip to content

Instantly share code, notes, and snippets.

@agawronski
Created December 11, 2017 05:42
Show Gist options
  • Save agawronski/500dbd6d00bcff06bc0bd4d3c1b706f0 to your computer and use it in GitHub Desktop.
Save agawronski/500dbd6d00bcff06bc0bd4d3c1b706f0 to your computer and use it in GitHub Desktop.
How many orders are shipped by each method in the entire dataset?
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment