Skip to content
Advertisement

postresql query to check no. of documents is 4

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
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement