aggregation2.sql

SELECT goods_id, sub_category, each.category, record_number, total_amount, total_sales
FROM sales_slip_10k each
JOIN
(
  SELECT 
    category, 
    COUNT(1) AS record_number, 
    SUM(amount) total_amount, 
    SUM(amount * price) AS total_sales
  FROM sales_slip_10k 
  GROUP BY category
) aggr
ON each.category = aggr.category
ORDER BY goods_id, sub_category, each.category
LIMIT 10