Q:

Multi join find count subquery

I have the below tables and have to write a query to get the below output . I am able to get all the columns of the output except for opportunity_published_count . This is basically a count of opportunities which are having value true. 
Current Query :
SELECT count(distinct opportunity_vendors.opportunity_id) as opportunity_count , 
       vendors.name as vendor_name  ,
       count(applications.id) as applications_count
FROM public.vendors 
  inner join public.opportunity_vendors on vendors.id = opportunity_vendors.vendor_id
  inner join public.opportunities on opportunity_vendors.opportunity_id = opportunities.id
  inner join public.applications on opportunity_vendors.opportunity_id = applications.opportunity_id
group by vendors.name;

Table Structure 
vendors
[id (PK) , vendor_name]
opportunity_vendors
[id (PK), opportunity_id,vendor_id] opp_id FK to opportunity , vendor_id FK to vendors
opportunities
[id (PK),opp_name,published] published is either true or false
applications
[id (PK),opportunity_id] opp_id FK to opportunity

Output 
vendor_name | opportunity_count | opportunity_published_count | applications_count

A:

Length is not an aggregate funtion, so you can use it in WHERE clause
Try This
SELECT count(DISTINCT opportunity_vendors.opportunity_id) as opportunity_count , 
       vendors.name as vendor_name  ,
       count(applications.id) as applications_count,
       count(case when published = true then opportunities.id end) as opportunity_published_count , 
FROM public.vendors 
  inner join public.opportunity_vendors on vendors.id = opportunity_vendors.vendor_id
  inner join public.opportunities on opportunity_vendors.opportunity_id = opportunities.id
  inner join public.applications on opportunity_vendors.opportunity_id = applications.opportunity_id
WHERE length(vendors.name) > 0
group by vendors.name

