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