I'm trying to solve below problem

CREATE TABLE names( id INT primary key, place VARCHAR2(15), opinion VARCHAR2(2));

/* Create few records in this table */
INSERT INTO names VALUES (1, 'mount', 'r');
INSERT INTO names VALUES (2, 'mount', 'nr');
INSERT INTO names VALUES (3, 'cod', 'r');
INSERT INTO names VALUES (4, 'cod', 'r');
INSERT INTO names VALUES (5, 'cod', 'r');
INSERT INTO names VALUES (6, 'qr', 'r');
INSERT INTO names VALUES (7, 'qr', 'nr');
INSERT INTO names VALUES (8, 'cafe', 'nr');
INSERT INTO names VALUES (9, 'mount', 'r');

want to get places in output, whose respective count (r - nr) is greater than or equal to one.

• If only r is present for a specific place, then we should that place in output.
• If only nr is present for a specific place, then we should not that place in output
• If both are present, then consider place whose (r-nr) >=1

not able to think after this

SELECT place, opinion, COUNT(*) as cnt
  FROM names
 GROUP BY place, opinion;

output names

mount
cod

CodePudding user response：

One option is using SIGN(SUM(DECODE())) combination along with HAVING clause while grouping by place column such as

SELECT place
  FROM names
 GROUP BY place
HAVING SIGN( SUM( DECODE(opinion,'r',1,'nr',-1) ) ) = 1

where

• DECODE() seperates the cases as numerical values
• SUM() determines total occurence difference
• SIGN() determines whether each difference is a positive integer or not

Demo

CodePudding user response：

Using a conditional aggregation

select place
from names
group by place
having sum(case opinion when 'r' then 1 when 'nr' then -1 end) >= 1