Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2007
    Posts
    65

    Unanswered: Count records from different tables

    Hello to all.
    This is new to me so i need some help.
    Im using sqlplus on Oracle RDBMS 11g. I have two tables:
    AP_INVOICES
    AR_RECEIPTS
    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 the COUNT (number of records) entered by the same creator?
    Many thanks in advance for any kind help.
    Octavio

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    select count(*)
    from AP_INVOICES
    where CREATED_BY = 'OTEIXEIRA'

  3. #3
    Join Date
    Jun 2007
    Posts
    65
    MCrowley, thanks for answering. Fortunately, i got the answer from another source and here it is:

    SELECT "Type","Inv/Rec date", "Date created", "Weekday", COUNT(*) "NR Registos"
    FROM (
    select decode(invoice_id, null,'','Invoice')"Type", invoice_date "Inv/Rec 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 decode(cash_receipt_id, null,'','Receipt') "Type", receipt_date "Inv/Rec 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 "Type","Inv/Rec date", "Date created", "Weekday"
    order by "Date created" asc

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It doesn't have to be that complicated.

    Code:
    select 'invoices',created_by,count(*)
    from ap_invoices
    group by created_by
    union all
    select 'receipts',created_by,count(*)
    from ar_cash_receipts_all
    group by created_by
    order by 2,1
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Jun 2007
    Posts
    65
    Beilstwh, absolutely agree with you.
    This is just the consequence of learning sqlplus by myself...
    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •