How to use ROLLUP and ORDER BY to generate additional TOTAL row?
I want to generate a list of all product_name(s) in the Products table as well as add an additional roll with the "total" price using ROLLUP.
The "total" price is calculated by subtracting the item_price from discount then multiplying by quantity of the items bought. Both Products table and Order_Items table are joined with the product_id which is present in both tables.
My SQL query is as follows:
SELECT product_name, (item_price - discount) * quantity AS "total" FROM order_items JOIN products ON products.product_id = order_items.product_id GROUP BY ROLLUP (product_name, "total amount");
The problem I'm getting is that SQL developer is telling me that my SELECT list is incosistent with GROUP BY and that "total amount" alias isn't valid in GROUP BY clause.
My intended output is something like
product_name total price product 1 $ 100.00 product 2 $ 300.00 product 3 $ 500.00 TOTAL $ $900.00 <- generated using ROLLUP
I just need help with wording my GROUP BY and using ROLLUP.