CODEWITHSUNDEEP
mysqlsqlpostgresqlspring-data-jpa
Ladu anand
Ladu anand

Reputation: 650

Write count query for a query which has multiple joins

For Spring JPA , I am using the below native query and I need to implement pagination . For that , I need to have a count query which will return the rows returned by below query . How do I write the count query.

SELECT  count(distinct opportunity_vendors.opportunity_id) as oppcount, vendors.name , count(applications.id) as appcount
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 having length(vendors.name) > 0;```

Upvotes: 0

Views: 59

Answers (2)

Bjarni Ragnarsson
Bjarni Ragnarsson

Reputation: 1781

You can do something like this:

select count(*) from (
SELECT  count(distinct opportunity_vendors.opportunity_id) as oppcount, vendors.name , count(applications.id) as appcount
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 having length(vendors.name) > 0
) d

Best regards,
Bjarni

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

You could use the query as a subquery .. a simple way is 

select count(*) 
from (

    SELECT  count(distinct opportunity_vendors.opportunity_id) as oppcount
        , vendors.name 
        , count(applications.id) as appcount
    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 
      having length(vendors.name) > 0; 

) t

Upvotes: 0

Related Questions