Count records from two tables [message #642472] |
Fri, 11 September 2015 08:30 |
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 #642481 is a reply to message #642476] |
Fri, 11 September 2015 09:07 |
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
|
|
|
|