DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Count(*)

  1. #1
    Join Date
    Jul 2001
    Posts
    334

    Count(*)

    Hi,
    I have 3 tables

    BATCHES(pk batch_id)
    INVOICES(pk invoice_id, fk batch_id)
    INVOICES_LINES(fk invoice_id)

    Consider we have batch and it has 6 invoices and each invoice has 10 lines. We need to count the invoice under the batch and also count the lines of all invoices.

    The out put I am looking is
    Batch Invoice lines
    ----- ------- -----
    INV 6 60

    What query I can write to get the above result.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    select i.batch_id,
    count(distinct i.invoice_id) invoice,
    count(*)
    from invoices i, invoice_lines il
    where i.invoice_id = il.invoice_id
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Code:
    select b.batch_name,
          (select count(*) from invoices i where b.batch_id = i.batch_id ) cnt_inv,
          (select count(*) from invoices i,
                                invoices_lines l
           where b.batch_id = i.batch_id and
                 i.invoice_id = l.invoice_id ) cnt_lines
    from batches b;

  4. #4
    Join Date
    Jul 2001
    Posts
    334
    Hi Shestakov,
    Thanks of your reply, Can we use this technique in 7.3.4, because when I run this query I get the below syntax error.

    ERROR at line 2:
    ORA-00936: missing expression

    Thanks,

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, you can't use this in 7.3. But what's wrong with slimdave's suggestion? You can use it with any Oracle release you want...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    May 2002
    Posts
    108

    But what's wrong with slimdave's suggestion?


    select i.batch_id,
    count(distinct i.invoice_id) invoice,
    count(*)
    from invoices i, invoice_lines il
    where i.invoice_id = il.invoice_id



    Less complicated query and will yield the expected output.
    But shouldn't it also include a GROUP BY clause for the i.Batch_id column?!

    select i.batch_id, count(distinct i.invoice_id) invoice,
    count(*) from invoices i, invoice_lines il
    where i.invoice_id = il.invoice_id
    GROUP BY i.Batch_id

    - Nandu
    Never give up !

    Nanda Kumar - Vellore

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: But what's wrong with slimdave's suggestion?

    ... shouldn't it also include a GROUP BY clause for the i.Batch_id column?! ...
    pesky details! i suppose so.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jul 2001
    Posts
    334
    There is nothing wrong with slimdave's suggestion. The only things I was investigating why we can not use SELECT in a column.

    * Slimdave, Thanks for you help, I really appreciate.

    Thanks.

Posting Permissions

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


Click Here to Expand Forum to Full Width