select query and same query with having results

Question

I have faced some "complicated" query, like:

with q1 as (
select w.entity_id, p.actionable_group, p.error_type
 from issue_analysis p
 join log l on p.id = l.issue_analysis_id
 join website w on l.website_id = w.id
 where l.date >= '2016-06-01'
 group by 1, 2, 3
),
q2 as (
select w.entity_id, p.actionable_group, p.error_type
 from issue_analysis p
 join log l on p.id = l.issue_analysis_id
 join website w on l.website_id = w.id
 where l.date >= '2016-06-01'
 group by 1, 2, 3
 having count(1) = 1
)

And tried to

SELECT q1.entity_id, count(q1.entity_id), count(q2.entity_id)
from q1, q2
group by 1
order by 1

but result provides for me a "wrong" data, cause it's not really contains both counts...

could you please describe the most "cleaner" way to solve such issue without a lot of nested queries?

if it may be helpful - q2 is similar to q1 but with having count(1) = 1 at the end.

P.S. Documentation link will be fine is the answer is simple.

Answer 1

You are, no doubt, getting a Cartesian product and this affects the aggregation. Instead, aggregate before doing the joins:

select q1.entity_id, q1_cnt, q2_cnt
from (select q1.entity_id, count(*) as q1_cnt
      from q1
      group by q1.entity_id
     ) q1 join
     (select q2.entity_id, count(*) as q2_cnt
      from q2
      group by q2.entity_id
     ) q2
     on q1.entity_id = q2.entity_id;