191116.sql

-- s.v.p. return the sum (gross) of sales ($) by category name! Use the 'total' col from sales
SELECT
  p.category_name
  ,SUM(s.total)::money
FROM
  sales s
LEFT JOIN
  products p on p.item_no = s.item
GROUP BY 1
ORDER BY 2 DESC
LIMIT 20;

-- s.v.p. return the avg proof and max bottle size by category name!
SELECT
  p.category_name
  ,ROUND(AVG(p.proof::numeric), 2) AS "proof"
  ,MAX(p.bottle_size) AS "bottle_size"
FROM
  sales s
LEFT JOIN
  products p on p.item_no = s.item
GROUP BY 1
ORDER BY 2, 3 DESC
LIMIT 20;

-- CHALLENGE! Create a new column, 'groce' which is the bottle_qty times the btl_price! Format as money!
SELECT
  btl_price
  ,bottle_qty
  ,(btl_price * bottle_qty)::money AS "groce"
  ,total::money
FROM sales
LIMIT 10;