Home » SQL & PL/SQL » SQL & PL/SQL » Count records from two tables (RDBMS 11G, EBS 11i)
Count records from two tables [message #642472] Fri, 11 September 2015 08:30 Go to next message
oteixeira
Messages: 33
Registered: May 2007
Member
Hello to all.
This is new to me so i need some help.
I'm using sqlplus on Oracle RDBMS 11g. I have two tables:
AP_INVOICES_ALL
AR_CASH_RECEIPTS_ALL
These two tables have nothing in common. However they both have a column with the same name and purpose (the CREATED_BY column).

How can I, is a single row, return values of some table columns ( like INVOICE_DATE and RECEIPT_DATE) AND the COUNT (number of records) entered by the same creator?

In other words, i have these TWO QUERIES, which work perfectly. My goal is to know if there is a way of getting the same result in a SINGLE query.

select invoice_date "Invoice date", trunc(creation_date) "Date created", to_char(creation_date, 'DAY') "Weekday", count(*) "Invoices entered"
from ap_invoices_all
where invoice_date > = '1-aug-15'
and created_by = 2960
group by invoice_date, trunc(creation_date), to_char(creation_date, 'DAY')
order by trunc(creation_date) asc

select receipt_date "Receipt date", trunc(creation_date) "Date created", to_char(creation_date, 'DAY') "Weekday", count(*) "Receipts entered"
from ar_cash_receipts_all
where receipt_date > = '1-aug-15'
and created_by = 2960
group by receipt_date, trunc(creation_date), to_char(creation_date, 'DAY')
order by trunc(creation_date) asc


Many thanks in advance for any kind help.
Octavio
Re: Count records from two tables [message #642476 is a reply to message #642472] Fri, 11 September 2015 08:33 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Do you want one column for the count or one column for invoice count and one column for receipt count?
Re: Count records from two tables [message #642479 is a reply to message #642476] Fri, 11 September 2015 08:51 Go to previous messageGo to next message
oteixeira
Messages: 33
Registered: May 2007
Member
Thanks for helping.
I would like to have the count on a single column. Perhaps i will add another to distinguish between Invoices and Receipts.
Thank you.
Re: Count records from two tables [message #642481 is a reply to message #642476] Fri, 11 September 2015 09:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well the solution differs depending whether you want that extra column or not. But assuming you don't:
SELECT "Invoice date", "Date created", "Weekday", COUNT(*)
FROM (
select invoice_date "Invoice date", trunc(creation_date) "Date created", to_char(creation_date, 'DAY') "Weekday"
from ap_invoices_all
where invoice_date > = '1-aug-15'
and created_by = 2960
UNION ALL
select receipt_date "Receipt date", trunc(creation_date) "Date created", to_char(creation_date, 'DAY') "Weekday"
from ar_cash_receipts_all
where receipt_date > = '1-aug-15'
and created_by = 2960
)
group by "Invoice date", "Date created", "Weekday"
order by "Date created" asc

Re: Count records from two tables [message #642484 is a reply to message #642481] Fri, 11 September 2015 09:21 Go to previous message
oteixeira
Messages: 33
Registered: May 2007
Member
Cokiemonster, thanks a million. I added a column for the record type. Works like a charm.
Would bye you a beer, if i could. Smile
Previous Topic: Consolidated output from a LOOP
Next Topic: String longer than 4000 chars inside cursor-for loop
Goto Forum:
  


Current Time: Fri Mar 29 04:23:36 CDT 2024