-
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.
-
select i.batch_id,
count(distinct i.invoice_id) invoice,
count(*)
from invoices i, invoice_lines il
where i.invoice_id = il.invoice_id
-
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;
-
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,
-
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?
-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|