Solved

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

Posted on 2015-01-28
16
235 Views
Last Modified: 2015-02-12
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
0
Comment
Question by:bcarlis
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 333 total points
ID: 40575603
select sum(case when type='REVENUE' then cost_amt else 0 end),
sum(case when type='UNEARNED' then cost_amt else 0 end)
from NOP_Earned_Rev
where TYPE in ( 'REVENUE','UNEARNED')
group by order_num
0
 
LVL 2

Author Comment

by:bcarlis
ID: 40575960
yes.. for those two.. I spaced on the CASE.. duh.. thank you.

But, what about 8 other tables.. 8 different columns.
was thinking subselects.. or unions using all accum. columns - unused as nulls ??

thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40575967
>>But, what about 8 other tables.. 8 different columns.

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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40575980
Can you post your 10 queries and what you would like the output to look like?
0
 
LVL 2

Author Comment

by:bcarlis
ID: 40576008
8 other separate sum() group by order_num queries
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40576021
If all the tables involved had an order_num column and you want sums for a provided order_num, just join all the tables and do a regular SUM.

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.
0
 
LVL 2

Author Comment

by:bcarlis
ID: 40576072
ok, here are the order numbers

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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40576083
>>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
...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

Expert Comment

by:awking00
ID: 40577877
Is the order_number unique in oe_order_headers_all or are there duplicates?
0
 
LVL 2

Author Comment

by:bcarlis
ID: 40579555
Yes, the order_number is unique in oe_order_headers_all.
Thank you..
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 333 total points
ID: 40579644
Please post sample data and expected results.

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_num
           join tab2 on ooh.order_num=tab2.order_num
           join tab3 on ooh.order_num=tab3.order_num
...
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 167 total points
ID: 40580453
I agree with slightwv that it is likely to be simpler than this, but without sample data and expected results, it's hard to say.
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
0
 
LVL 2

Author Comment

by:bcarlis
ID: 40581736
Thank you for your help..
 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..
0
 
LVL 2

Author Comment

by:bcarlis
ID: 40581776
Ok,
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;

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40581944
>>I would like to not use oe_order_headers_all because it has millions of rows.

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.
0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 40605956
Thanks for the help!
I didn't want to create result data..
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to take different types of Oracle backups using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now