Counts from Multiple tables 1

[prismtx]

I have several tables that I want to get counts from and display on a single line.

Table1: order_date, item_ordered, etc
Table2: Process_date, item_processed, etc
Table3: Shipment_date, package_count, etc
Table4: Payment_date, amount_received, etc

What I want is:
Date Order Count Processed Shipments Pmnts Rcvd
01/05 50 45 61 22
01/06 39 51 34 26
01/07 40 42 41 30

If I run each sql separately, I can get the correct counts, but if I try to combine them, the sql eventually times out.

Here is a sample of one of the individual sql's. There are no fields common to the tables, but the dates although named differently have the same values for what I want to select.

select (substr(to_char(order_date), 5, 2) || '/' ||
substr(to_char(order_date), 7, 2) || '/' ||
substr(to_char(order_date), 1, 4)) as "Date",
count(*) as "Order Count"
from table1 ord
where order_date between to_char(sysdate, 'yyyymmdd') and
to_char(sysdate - 3, 'yyyymmdd')
group by order_date
order by to_date(order_date, 'yyyymmdd');

Any suggestions? I'm running it via SqlPlus.

 

[SantaMufasa]

Prismtx,

Yes, we can make very efficient code to do what you want, but to test the code and results, I'll need some sample data. If you post working INSERT statements for the four tables, I'll be glad to post code to do what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[prismtx]

Here are some inserts and sample test data. The actual tables have around several million records in them:

insert into order
(ORDER_DATE, CAR_CODE, TICKET, ITEM_ORDERED, ITEM_SUFFIX)
values (20080105', 'WX', 'A123', '13990', 'FF')
values (20080106', 'WX', 'A123', '13550', ' ')
values (20080106', 'WJ', 'A156', '13560', 'FA')
values (20080107', 'WX', 'A124', '13990', '04')

insert into process
(PROCESS_DATE, EMPID, TICKET, ITEM_PROCESSED, ITEM_SUFFIX)
values (20080105', '854822', 'W236', '13245', 'L1')
values (20080105', '244543', 'K943', '01205', 'RR')
values (20080106', '543456', 'A121', '16220', 'AS')
values (20080107', '142332', 'A109', '53960', 'PL')
values (20080107', '142332', 'B122', '53960', 'PL')

insert into shipment
(SHIPMENT_DATE, TICKET, PACKAGE_COUNT)
values (20080104', 'A123', 1)
values (20080105', 'A123', 1)
values (20080106', 'A156', 3)
values (20080107', 'A156', 3)
values (20080107', 'A124', 2)

insert into payment
(PAYMENT_DATE, INVOICE, INV_AMT, PMT_RECD)
values (20080105', 'T12778', 80000, 80000)
values (20080105', 'T12437', 54321, 53100)
values (20080106', 'T12563', 4332, 4332)
values (20080107', 'T12731', 5078, 5078)

 

[SantaMufasa]

I must take my wife to a doctor's appointment presently. I shall be back to work on this in about 2 hours from this posting.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

(I'm still at the doctor's office, but he let me use his computer to post a response to you. Thanks, Doc !)

Here is a query that does what you requested, followed by results based upon the INSERTs you provided:

col dt heading "Date"
col a heading "Order|Count"
col b heading "Processed"
col c heading "Shipments"
col d heading "Pmnts|Rcvd"
select dt, max(a) a, max(b) b, max(c) c, max(d) d
  from (select order_date dt, count(*) a, null b, null c, null d
          from ord group by order_date
  union select process_date dt, null, count(*), null, null
          from process group by process_date
  union select shipment_date dt, null, null, count(*), null
          from shipment group by shipment_date
  union select payment_date dt, null, null, null, count(*)
          from payment group by payment_date)
group by dt;

Order                            Pmnts
Date           Count  Processed  Shipments       Rcvd
--------- ---------- ---------- ---------- ----------
04-JAN-08                                1
05-JAN-08          1          2          1          2
06-JAN-08          2          1          1          1
07-JAN-08          1          2          2          1

Let us know if you have questions about the syntax or logic of the code.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

LOL. Above and beyond the call of duty Dave (as always) Kudos. Prismtx, I hope you remeber to give Dave another star to thank him

 

[prismtx]

I Gave you a Star. As Jim said, you certainly went above and beyond on answering this for me! I added in my date selection and got the results back in 10 seconds!

Many thanks again!
Dennis

 

[SantaMufasa]

Thanks to both of you!

Yes, I am continually amazed at how fast Oracle set operators (UNION, MINUS, INTERSECT, et cetera) behave.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Can I suggest however, that a better 'habit' to get into when performing union queries, would be to use UNION ALL instead of union unless explicilty wanting to get rid of duplicates from the reultset. UNION all will generally perform better than union as no sort is required. Just my 2c