I have a master table for applications and another table for documents upload, I want the count of applications which has 4 documents in document table.
master application table=>application_ht_install and Documents table=>bescom_appl_upload_doc ack_no is the primary key for application_ht_install and foreign key for application Documents.
And doc_id is for the document id, Help me out guys
x
select count(distinct
case when ack_no in
(select ack_no from bescom_appl_upload_doc) then ack_no end
) as pending_with_documents
from application_ht_install where service_code in (36) and status_code in ('ACK','INT')
the above query gives count for application with documents, and I want count of application in application_ht_install which has 4 documents in bescom_appl_upload_doc
Advertisement
Answer
here is one way :
select count(*)
from application_ht_install A
where
service_code in (36)
and status_code in ('ACK', 'INT')
and (select count(*) from bescom_appl_upload_doc b where a.id = b.id ) = 4
replace a.id = b.id
with right connection between tables
well, that would be a new question but I answer it here anyways:
select sum(case when docCounter= 4 then 1 end) countwith4doc
,sum(case when docCounter< 4 then 1 end) countwithlessthan4doc
from (
select a.id, count(*) docCounter
from application_ht_install A
join bescom_appl_upload_doc b
on a.id = b.id
and a.service_code in (36)
and a.status_code in ('ACK', 'INT')
group by a.id
) t