How can I compare the counts for the below table and only output those that have a difference?

CodePudding user response：

Here's one option:

Sample data:

SQL> with test (doc, variable, cnt) as
  2    (select 'doc1', 'pstart',  8 from dual union all
  3     select 'doc1', 'pend'  ,  8 from dual union all
  4     select 'doc2', 'pstart',  5 from dual union all
  5     select 'doc2', 'pend'  ,  6 from dual union all
  6     select 'doc3', 'pstart', 12 from dual union all
  7     select 'doc3', 'pend'  , 12 from dual
  8    )

Query begins here:

  9  select doc, sum(case when variable = 'pstart' then cnt end) pstart,
 10              sum(case when variable = 'pend'   then cnt end) pend
 11  from test
 12  group by doc
 13  having sum(case when variable = 'pstart' then cnt end) <>
 14         sum(case when variable = 'pend'   then cnt end);

DOC      PSTART       PEND
---- ---------- ----------
doc2          5          6

SQL>

CodePudding user response：

This is one way to solve it:

 select max(doc),max(variable),"count(*)" from your_table
 group by "count(*)" having count(*)=1