SQL script help 1

[ridhirao22]

Hi,

Trying to find percentage of orders shipped to more than one address, more than two address and more than three address for a year range.
tables I have are orders, orderline,Shippment

I have used count of address by order id and used having clause as inline view in the from clause for the shippment table. Done this 3 times. then join back to the orders table.

Can any one help me the better apporach. Sorry I don't have access to the script I would post it here for ex. Hope I could explain better than this.

TIA
RR

 

[SantaMufasa]

RR,

When you say "more than one address...", do you want the number of orders with exactly 2 addresses, or do you want the "more than one address" to include the orders with 2+3+4+more addresses? And to continue the line of thought, should the "more than two addresses" be the orders with exactly 3 addresses or should the number include orders with 3+4+more addresses?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ridhirao22]

My understanding to requirment to as to include the orders with 2+3+4+more addresses? And to continue the line of thought, should the "more than two addresses" be the orders with exactly 3 addresses or should the number include orders with 3+4+more addresses.

Thanks,
RR

 

[ridhirao22]

Also, I forgot to mention in the above post. will need to find the percentage of oders that are shipped to more than 1 address have different shipping methods. TIA

 

[Dagon]

I'm not exactly sure what you're requirements (and you don't entirely seem to know yourself, judging by comment 2). However, as a general principle, you could do something like this rather than having lots of table joins:

drop table shipments;

create table shipments (order_id number, address varchar2(50));

insert into shipments values (1, 'Northern Ireland');
insert into shipments values (1, 'England');
insert into shipments values (1, 'Scotland');
insert into shipments values (1, 'Wales');
insert into shipments values (2, 'Germany');
insert into shipments values (2, 'France');
insert into shipments values (2, 'Italy');
insert into shipments values (3, 'Papua New Guinea');
insert into shipments values (3, 'Thailand');
insert into shipments values (4, 'Zimbabwe');
insert into shipments values (4, 'China');
insert into shipments values (5, 'Liberia');

select sum(more_than1), sum(more_than2), sum(more_than3) 
from
(select order_id,
       count(case when num_address > 1 then 1 end) as more_than1,
       count(case when num_address > 2 then 1 end) as more_than2,
       count(case when num_address > 3 then 1 end) as more_than3
from
(       
select order_id, 
       count(address) num_address
from shipments
group by order_id)
group by order_id)

For Oracle-related work, contact me through Linked-In.

 

[ridhirao22]

Thank you, Dagon and Mufasa, I wish I could think like you guys. Your explaination is very easy to understand even though people like me have tough time list out our issue excatly.

Have another question. need to find orders paid by creditcard and gift cards( in combination). Any help is appreicated.

Order has order line and order line has product( giftcard is listed as product code.

Order has payment and payment has Credit card information.

I am too vague but I am new in this and trying to learn.

Thanks again!
RR

 

[SantaMufasa]

RR,

First, Dagon deserves a

for his work. (I only was trying to understand your need, but Dagon provided a helpful solution.) Press the link, [Thank Dagon for this valuable post!]

As per your second issue, please open up a new thread for that, along with "CREATE TABLE...", sample data in "INSERT INTO..." statements, and sample output.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ridhirao22]

I thought I already gave him a star. I didn't se it either, How does it show up?

 

[SantaMufasa]

It worked, RR. Well done.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”