CODEWITHSUNDEEP
oracle-databasejoingroup-by
wsams
wsams

Reputation: 2647

For each row of one table, count entries in another table pointing to each of those rows in Oracle

Not sure the title explains the problem well; this is what I'm working with,

I have the following tables,

-- table = kms_doc_ref_currnt_v
DOC_ID    VARCHAR2(19)
TO_DOC_ID    VARCHAR2(19)
BRANCH_ID    NUMBER(8)
REF_TYP_CD    VARCHAR2(20)

-- table = kms_fil_nm_t
DOC_ID    VARCHAR2(19)    PRIMARY KEY    UNIQUE

For example, I can get a count of all kms_doc_ref_currnt_v records that have a to_doc_id = 59678, where 59678 is one value in kms_fil_nm_t, with this query,

select 'doc_id 59678 has ' || count(to_doc_id) as cnt from kms_doc_ref_currnt_v where branch_id=1 and ref_typ_cd in ('CONREF', 'KBA') and to_doc_id=59678;

kms_doc_ref_currnt_v.to_doc_id is a field that has one of the kms_fil_nm_t.doc_id values. kms_doc_ref_currnt_v.doc_id is also one of the values in kms_fil_nm_t.

The single query I'm looking for would loop over each kms_fil_nm_t.doc_id and count all the rows in kms_doc_ref_currnt_v that have a similar to_doc_id. Each row returned would look like the output of the query above. Here's example output,

doc_id 1 has 32
doc_id 2 has 314
doc_id 3 has 2718
doc_id 4 has 42
doc_id 5 has 128
doc_id 6 has 11235
.
.
.

Probably simple but I just can't figure it out.

Upvotes: 0

Views: 168

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

You need to use an outer join between the driving table that has all the doc_id values and the dependent table that may or may not have matching entries; and a group by clause to define what your aggregate function (count()) is operating against. Something like:

select 'doc_id ' || t.doc_id || ' has ' || count(*)
from kms_fil_nm_t t
left join kms_doc_ref_currnt_v v
on v.to_doc_id = t.doc_id
and v.branch_id = 1
and v.ref_typ_cd in ('CONREF', 'KBA')
group by t.doc_id;

This assumes you want to know when a doc_id isn't used, so you want entries like doc_id 1234 has 0. If you don't want to see those then you could use an inner join instead of an outer - essentially just remove the word left - but if that is the case then you don't really need to join at all, you could just do:

select 'doc_id ' || v.to_doc_id || ' has ' || count(*)
from kms_doc_ref_currnt_v v
where v.branch_id = 1
and v.ref_typ_cd in ('CONREF', 'KBA')
group by v.to_doc_id;

Unless there are to_doc_id values which are not in the other table, which would be included in the results of this query, but excluded if the tables were joined.

Upvotes: 1

Yogendra Singh
Yogendra Singh

Reputation: 34387

Do a join with two tables and add a GROUP BY clause as below:

    SELECT 'doc_id 59678 has ' || count(to_doc_id) as cnt 
    FROM kms_doc_ref_currnt_v kv, kms_fil_nm_t kt
    WHERE kt.doc_id= kv.to_doc_id 
          AND kv.branch_id=1 
          AND kv.ref_typ_cd in ('CONREF', 'KBA') 
          AND kv.to_doc_id=59678
    GROUP BY kv.to_doc_id;

EDIT:

To get all records from kms_doc_ref_currnt_v irrespective of their reference availability in kms_fil_nm_t and kv.to_doc_id=59678, do like this:

    SELECT 'doc_id 59678 has ' || count(to_doc_id) as cnt 
    FROM kms_doc_ref_currnt_v kv 
         LEFT JOIN kms_fil_nm_t kt
         ON (kt.doc_id= kv.to_doc_id )
    WHERE  kv.branch_id=1 
          AND kv.ref_typ_cd in ('CONREF', 'KBA') 
    GROUP BY kv.to_doc_id;

to replace the hardcoding 59678, you may want to write:

    SELECT 'doc_id ' || kt.doc_id || ` has ' || count(to_doc_id) as cnt 
    FROM kms_doc_ref_currnt_v kv 
         LEFT JOIN kms_fil_nm_t kt
         ON (kt.doc_id= kv.to_doc_id )
    WHERE  kv.branch_id=1 
          AND kv.ref_typ_cd in ('CONREF', 'KBA') 
    GROUP BY kv.to_doc_id, kt.doc_id;

Upvotes: 1

Related Questions