Your question, your audience. Choose who sees your identity—and your question—with question security.

Hi,

What is the best way to create a view needing to have 10 different sum() group by queries all having order number in common?

Was going to use PARTITION BYs

but 2 of the queries have same table and a where statement

select sum(cost_amt) from NOP_Earned_Rev

where TYPE = 'REVENUE'

group by order_num

select sum(cost_amt) from NOP_Earned_Rev

where TYPE = 'UNEARNED'

group by order_num

8 other separate queries with order_num

Thank you,

Bill

What is the best way to create a view needing to have 10 different sum() group by queries all having order number in common?

Was going to use PARTITION BYs

but 2 of the queries have same table and a where statement

select sum(cost_amt) from NOP_Earned_Rev

where TYPE = 'REVENUE'

group by order_num

select sum(cost_amt) from NOP_Earned_Rev

where TYPE = 'UNEARNED'

group by order_num

8 other separate queries with order_num

Thank you,

Bill

sum(case when type='UNEARNED' then cost_amt else 0 end)

from NOP_Earned_Rev

where TYPE in ( 'REVENUE','UNEARNED')

group by order_num

But, what about 8 other tables.. 8 different columns.

was thinking subselects.. or unions using all accum. columns - unused as nulls ??

thanks

What 8 other tables?

If you mean 8 other columns just add 3-10 just like the two I added.

If you mean something else, please post the additional information.

Just making a dashboard with all these totals per order_num

select (select sum(col1) from a where a.order_num = <same as others> group by order_num) col1

, (select sum(col2) from b where b.order_num = <same as others> group by order_num) col2

, (select sum(col3) from c where c.order_num = <same as others> group by order_num) col3

I can't think..

10 columns.. all have sums from different tables to show for an order number

"order number", col1, col2, col3, col4, col5, col6, col7, col8, col9, col10

i can't pick any of the tables as the one that has all the order numbers.

No need for individual inline selects.

However,

We will need to see sample data, tables, expected results, etc... to be able to provide more of a copy/paste solution.

select oha.ORDER_NUMBER,

from oe_order_headers_all oha

thinking

select oha.ORDER_NUMBER, (select sum(colA) from abc where abc.ORDER_NUMBER = oha.ORDER_NUMBER) col1

, (select sum(colS) from wbc where wbc.ORDER_NUMBER = oha.ORDER_NUMBER) col2

, (select sum(colZ) from aqc where aqc.ORDER_NUMBER = oha.ORDER_NUMBER) col3

, etc..

from oe_order_headers_all oha

all but the oe_order_headers_all table are custom tables with FK of order_number

oe_order_headers_all will show all available orders whether any of the custom tables have data or not..

open to better ideas?? does that give enough detail?

Not really.

I was looking for sample table descriptions, sample data and expected results.

Doesn't have to be 'real' data:

create table abc(ORDER_NUMBER number, col1 number);

insert into abc values(111111,1);

insert into abc values(222222,2);

...

expected results:

order_num col1 col2 col3

1111111 10 30 60

2222222 0 5 9

...

select o.order_number,a.col1, b.col2, c.col3,...,j.col10

from oe_order_headers_all o

left join

(select order_number, sum(somecolumn) col1 from sometable <somewhereclause> group by order_number) a

on o.order_number = a.order_number

left join

(select order_number, sum(somecolumn) col2 from sometable <somewhereclause> group by order_number) b

on o.order_number = b.order_number

left join

(select order_number, sum(somecolumn) col3 from sometable <somewhereclause> group by order_number) c

on o.order_number = c.order_number

...

left join

(select order_number, sum(somecolumn) col10 from sometable <somewhereclause> group by order_number) j

on o.order_number = j.order_number

I am making the create sample tables etc.. I am still designing this that is why I was asking theory level but now I understand much more about my client's needs..

results..

ok,

Expected results are like this..

"order number", total_cost1, total_cost2, total_cost3, total_cost4, total_cost5, total_cost6, total_cost7, total_cost8

order 5 will have 5 columns populated, orader 12 - 1 column, etc

I would like to not use oe_order_headers_all because it has millions of rows.

I can't pick any of the custom tables as the one that has all the order numbers.

Some order numbers are varchar because the numbers can be '123-FG'

```
drop table custom01;
drop table custom02;
drop table custom03;
drop table custom04;
drop table custom05;
drop table custom06;
drop table custom07;
drop table custom08;
drop table oe_order_headers_all;
create table custom01(ORDER_NUMBER number, cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom02(ORDER_NUMBER varchar2(100), price number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom03(ORDER_NUMBER varchar2(100), cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom04(ORDER_NUMBER number, cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom05(ORDER_NUMBER varchar2(100), cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom06(ORDER_NUMBER varchar2(100), cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom07(ORDER_NUMBER number, cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table custom08(ORDER_NUMBER number, cost number, col3 number, col4 varchar2(100), col5 varchar2(100));
create table oe_order_headers_all (ORDER_NUMBER varchar2(100));
insert into custom01 values(1, 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom01 values(1, 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom01 values(1, 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom01 values(1, 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom01 values(5, 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom01 values(6, 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom01 values(7, 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom01 values(8, 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom01 values(12, 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom01 values(23, 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom01 values(33, 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom01 values(34, 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom02 values('5', 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom02 values('5', 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom02 values('5', 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom02 values('14', 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom02 values('5', 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom02 values('5', 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom02 values('5', 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom02 values('5', 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom02 values('25', 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom02 values('63', 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom02 values('17', 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom02 values('28', 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom03 values('1', 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom03 values('1', 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom03 values('1', 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom03 values('1', 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom03 values('5', 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom03 values('5', 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom03 values('8', 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom03 values('8', 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom04 values(3, 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom04 values(3, 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom04 values(3, 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom04 values(8, 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom04 values(8, 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom04 values(8, 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom04 values(8, 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom04 values(8, 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom05 values('12345', 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom05 values('3', 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom05 values('3', 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom05 values('3', 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom05 values('5', 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom05 values('5', 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom05 values('7', 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom05 values('7', 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom06 values('4', 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom06 values('4', 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom06 values('4', 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom06 values('4', 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom06 values('4', 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom06 values('4', 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom06 values('4', 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom06 values('4', 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom07 values(1, 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom07 values(1, 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom07 values(1, 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom07 values(5, 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom07 values(5, 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom07 values(5, 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom07 values(7, 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom07 values(7, 8288.82, 2.800734, 'TestData 8', 'TestData 18');
insert into custom08 values(4, 1218.82, 2.100734, 'TestData 1', 'TestData 11');
insert into custom08 values(4, 2228.82, 2.200734, 'TestData 2', 'TestData 12');
insert into custom08 values(4, 3238.82, 2.300734, 'TestData 3', 'TestData 13');
insert into custom08 values(6, 4248.82, 2.400734, 'TestData 4', 'TestData 14');
insert into custom08 values(6, 5258.82, 2.500734, 'TestData 5', 'TestData 15');
insert into custom08 values(29, 6268.82, 2.600734, 'TestData 6', 'TestData 16');
insert into custom08 values(29, 7278.82, 2.700734, 'TestData 7', 'TestData 17');
insert into custom08 values(29, 8288.82, 2.800734, 'TestData 8', 'TestData 18');
begin
for j in 1..1000 loop -- actually this one has millions of rows
insert into oe_order_headers_all values(j);
end loop;
end;
```

But order_num should have an index on it so the lookup should be really quick. Also, millions of rows isn't a lot these days.

>>Expected results are like this..

Based on the data you provided: What are the 'actual' results? I was looking for the actual results not a description.

In the original question you had a where clause like: where TYPE = 'REVENUE'

In the followup post you had: where wbc.ORDER_NUMBER = oha.ORDER_NUMBER

I still think the regular join that has been proposed will be the best option but we'll need more information before we can provide working SQL.

Once we get that we can work on performance.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

It sounds like it is a simple join across all 10 tables and you are trying to make this harder than it needs to be.

I cannot say if it is that simple until I understand the tables and data you are dealing with.

Should go something like:

select ooh.order_num,

sum(tab1.column),

sum(tab2.column),

sum(tab3.column),

...

from oe_order_headers_all ooh

join tab1 on ooh.order_num=tab1.order_n

join tab2 on ooh.order_num=tab2.order_n

join tab3 on ooh.order_num=tab3.order_n

...