Multiple Tables Query-CodePudding

I have these tables and columns:

order_items: order_id, item_id, product_id, quantity, unit_price
product_categories: category_id, category_name
products: product_id, product_name, description, standard_cost, list_price, category_id

I need to write a SQL query to show the total and the average sale amount for each category.

So far, I have this but I do not know how to get the total and the average of all products per category:

SELECT 
    p.product_name, 
    oi.product_id, 
    pc.category_id, pc.category_name, 
    (oi.quantity * oi.unit_price) AS total_sale_amount,
    AVG(oi.quantity * oi.unit_price) AS average_sale_amount 
FROM 
    products p 
INNER JOIN 
    product_categories pc ON p.category_id = pc.category_id 
INNER JOIN 
    order_items oi ON oi.product_id = p.product_id 
GROUP BY 
    p.product_name, oi.product_id, pc.category_id, pc.category_name, 
    oi.quantity * oi.unit_price;

Maybe with WHERE and AND clauses having in mind the value of every category ID, however I do not know how to implement it.

category_id: 1, 2, 3, 4, 5

Thanks in advance.

CodePudding user response：

I figured it out, I was using more columns than the actually needed:

SELECT 
p.category_id, 
pc.category_name, 
SUM(oi.quantity * unit_price) AS total_sale_amount, 
ROUND(AVG(oi.quantity * unit_price), 2) AS average_sale_amount

FROM 
products p 

INNER JOIN 
order_items oi ON p.product_id = oi.product_id 
INNER JOIN 
product_categories pc ON pc.category_id =  p.category_id 

GROUP BY 
p.category_id, pc.category_name;

CodePudding user response：

If you're using MySQL you'd need WITH ROLLUP:

SELECT p.product_name, 
       oi.product_id, 
       pc.category_id, 
       pc.category_name, 
       SUM(oi.quantity * oi.unit_price) AS total_sale_amount,
       AVG(oi.quantity * oi.unit_price) AS average_sale_amount 
  FROM products p 
  INNER JOIN product_categories pc 
    ON p.category_id = pc.category_id 
  INNER JOIN order_items oi 
  ON oi.product_id = p.product_id 
  GROUP BY p.product_name,
           oi.product_id,
           pc.category_id,
           pc.category_name
    WITH ROLLUP

If you're using Oracle it's a little different:

SELECT p.product_name, 
       oi.product_id, 
       pc.category_id, 
       pc.category_name, 
       SUM(oi.quantity * oi.unit_price) AS total_sale_amount,
       AVG(oi.quantity * oi.unit_price) AS average_sale_amount 
  FROM products p 
  INNER JOIN product_categories pc 
    ON p.category_id = pc.category_id 
  INNER JOIN order_items oi 
  ON oi.product_id = p.product_id 
  GROUP BY ROLLUP(p.product_name,
                  oi.product_id,
                  pc.category_id,
                  pc.category_name)