aggregation.percent_of.sql

SELECT 
  sub_category, subcat.category,
  record_number_subcat, record_number_cat, 
  1.0*record_number_subcat/record_number_cat AS percent_of_record_number,
  total_amount_subcat, total_amount_cat, 
  1.0*total_amount_subcat/total_amount_cat AS percent_of_total_amount, 
  total_sales_subcat, total_sales_cat,
  1.0*total_sales_subcat/total_sales_cat AS percent_of_total_sales
FROM (
  SELECT 
    sub_category, category,
    COUNT(1) AS record_number_subcat, 
    SUM(amount) total_amount_subcat, 
    SUM(amount * price) AS total_sales_subcat
  FROM sales_slip_10k
  GROUP BY category, sub_category
) subcat
JOIN
(
  SELECT 
    category,
    COUNT(1) AS record_number_cat, 
    SUM(amount) total_amount_cat, 
    SUM(amount * price) AS total_sales_cat
  FROM sales_slip_10k
  GROUP BY category
) cat
ON subcat.category = cat.category
ORDER BY sub_category, subcat.category