Write count query for a query which has multiple joins

Question

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

Bjarni Ragnarsson · Accepted Answer · 2019-12-12 12:50:42Z

1

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

edited Dec 12, 2019 at 12:50

user2399035

answered Dec 12, 2019 at 12:50

Bjarni Ragnarsson

1,7811 gold badge8 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2019-12-12 12:49:28Z

0

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

answered Dec 12, 2019 at 12:49

ScaisEdge

134k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Write count query for a query which has multiple joins

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related