SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Subtotal/Total in SQL

s1206Jul 8 2020 — edited Jul 8 2020

Hello,

I have a table that has 6 columns of data, and I'd like a query that returns values that look like this:

PO	ITEM	QTY	USER	VENDOR	LPN
1	D1	50	JDOE	ABC COMPANY	L1
1	D1	65	JDOE	ABC COMPANY	L2
Subtotal	115
1	D2	20	JDOE	ABC COMPANY	L3
1	D2	25	JDOE	ABC COMPANY	L4
Subtotal	45
1	D3	10	JDOE	ABC COMPANY	L5
1	D3	12	JDOE	ABC COMPANY	L6
Subtotal	22
2	D3	15	JDOE	ABC COMPANY	L7
2	D3	20	JDOE	ABC COMPANY	L8
Subtotal	35

If I write this query:

select po, item, user_name, vendor, lpn, sum(qty)

from stest

where po = 1

group by rollup (po, item), user_name, vendor;

I get this as a result, which is definitely not what I want.

PO	ITEM	USER_NAME	VENDOR	LPN	SUM(QTY)
1	D1	JDOE	ABC COMPANY	L1	50
1	JDOE	ABC COMPANY	L1	50
JDOE	ABC COMPANY	L1	50
1	D1	JDOE	ABC COMPANY	L2	65
1	JDOE	ABC COMPANY	L2	65
JDOE	ABC COMPANY	L2	65
1	D2	JDOE	ABC COMPANY	L3	20
1	JDOE	ABC COMPANY	L3	20
JDOE	ABC COMPANY	L3	20
1	D2	JDOE	ABC COMPANY	L4	25
1	JDOE	ABC COMPANY	L4	25
JDOE	ABC COMPANY	L4	25
1	D3	JDOE	ABC COMPANY	L5	10
1	JDOE	ABC COMPANY	L5	10
JDOE	ABC COMPANY	L5	10
1	D3	JDOE	ABC COMPANY	L6	12
1	JDOE	ABC COMPANY	L6	12
JDOE	ABC COMPANY	L6	12

Is my desired result even possible in SQL?

This post has been answered by Frank Kulash on Jul 8 2020

Jump to Answer

Added on Jul 8 2020

7 comments

629 views