How to use ROLLUP and ORDER BY to generate additional TOTAL row?

Question

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.

I use SQL Developer to run my scripts – 5120bee Mar 04 '17 at 07:51 — 5120bee, Mar 04 '17 at 07:51

score 1 · Accepted Answer · answered Mar 04 '17 at 07:57

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);

answered Mar 04 '17 at 07:57

Gurwinder Singh

38,557
6
51
76

It still says "SELECT list inconsistent with GROUP BY" error – 5120bee Mar 04 '17 at 08:04
Also "TOTAL" is invalid identifier according to SQL developer – 5120bee Mar 04 '17 at 08:05
Oh, hang-on -- it matters if TOTAL is in single quotations 'TOTAL' or double quotations "TOTAL"? – 5120bee Mar 04 '17 at 08:07
I used 'TOTAL' (single quotations) not "TOTAL". You may have modified it – Gurwinder Singh Mar 04 '17 at 08:07
Oh yes, I used double quotes. It's working now for single quotes. Thank you! – 5120bee Mar 04 '17 at 08:09
P.S. My problem is solved but I wonder if there is another way to solve this without using COALESCE? – 5120bee Mar 04 '17 at 08:09
Yes there is an ugly way - using bunch of union all and aggregation without rollup. I think this one is clearer. – Gurwinder Singh Mar 04 '17 at 08:11

How to use ROLLUP and ORDER BY to generate additional TOTAL row?

1 Answers1