I don't know how to compare counted elements inside of ist and osv columns.

select 
  r.parent_id
, count(case when c.config_field_id=100 then c.key_value end) as ist
, count(case when c.config_field_id=101 then c.key_value end) as osv 
from relation as r 
left join config_value_number as c 
  on r.child_id = c.key_value 
where c.config_field_id in(100, 101) 
  and r.relation_type_id in(200, 201) 
group by parent_id

Result:

So here above I have to compare whether ist column and osv column values are same or not. Please help me to solve this problem. Thanks in advance.

CodePudding user response：

use this

select * from (
select r.parent_id, 
count(case when c.config_field_id=100 then c.key_value end) as ist, 
count(case when c.config_field_id=101 then c.key_value end) as osv 
from relation as r 
left join config_value_number as c on r.child_id = c.key_value 
where c.config_field_id in(100, 101) 
and r.relation_type_id in(200, 201) 
group by parent_id
)a
where ist=osv

CodePudding user response：

The trivial solution is to treat your current query as a table expression of a main query. Then, it's easy to perform the comparison.

For example:

select x.*,
  case when ist = osv then 'same' else 'diff' end as are_equal
from (
  -- your existing query here
) x

CodePudding user response：

Or you can use the standard predicate on an aggregation result: HAVING:

select 
  r.parent_id
, count(case when c.config_field_id=100 then c.key_value end) as ist
, count(case when c.config_field_id=101 then c.key_value end) as osv 
from relation as r 
left join config_value_number as c 
  on r.child_id = c.key_value 
where c.config_field_id in(100, 101) 
  and r.relation_type_id in(200, 201) 
group by parent_id
HAVING count(case when c.config_field_id=100 then c.key_value end)
     = count(case when c.config_field_id=101 then c.key_value end)
;

You don't even have to include the CASE expressions in the SELECT list - you can just evaluate them in the HAVING clause.