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.

Answers 1

  • You want to use sum on the second field, modify the rollup and coalesce (or NVL) on the first field to show TOTAL on rolled up row.

    Try this:

    select coalesce(product_name, 'TOTAL') Product_name,
        sum((item_price - discount) * quantity) as "total"
    from order_items
    join products on products.product_id = order_items.product_id
    group by ROLLUP(product_name);

Related Articles