Count(*)

[aph]

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.

[slimdave]

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

[Shestakov]

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;

[aph]

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,

[jmodic]

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...

[nandu]

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

[slimdave]

... shouldn't it also include a GROUP BY clause for the i.Batch_id column?! ...

pesky details! i suppose so.

[aph]

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.