SQL query: How to identify group differences

Saddy 0
Viewed 23
col1 col2 col3
group1 subgroup1 54
group1 subgroup1 31
group1 subgroup2 54
group1 subgroup2 55
group2 subgroup3 40
group2 subgroup3 41
group2 subgroup4 40
group2 subgroup4 41

Each group has a number of subgroups and each subgroup has a number of values.

I need to filter out the group that has the same value in col3. For example, group2 has values(40,41)

only return group1 as subgroup1(54,31) and subgroup2(54,55) shares different values(31, 55) in col3. How can I achieve this in SQL?

Desire result:

col1 col2 col3
group1 subgroup1 54
group1 subgroup1 31
group1 subgroup2 54
group1 subgroup2 55
sql-server sql
1 Answers

You could use string_agg(). ie:

select col1, col2, string_agg(col3, ',')
from mytable
group by col1, col2;

CREATE TABLE mytable (
  col1 VARCHAR(10),
  col2 VARCHAR(10),
  col3 varchar(10)
);

INSERT INTO mytable
  (col1, col2, col3)
VALUES
  ('group1', 'subgroup1', '54'),
  ('group1', 'subgroup1', '31'),
  ('group1', 'subgroup2', '54'),
  ('group1', 'subgroup2', '55'),
  ('group2', 'subgroup3', '40'),
  ('group2', 'subgroup3', '41'),
  ('group2', 'subgroup4', '40'),
  ('group2', 'subgroup4', '41');

select col1, col2, string_agg(col3, ',')
from mytable
group by col1, col2;
col1 col2 (No column name)
group1 subgroup1 54,31
group1 subgroup2 54,55
group2 subgroup3 40,41
group2 subgroup4 40,41

DBFiddle demo

EDIT: According to your edited and changed requirement:

with dummy as
(select col1, col2, string_agg(col3, ',') col3
from mytable
group by col1, col2
),
  unq as 
  (
select col3
from dummy
group by col3
having count(*) = 1
)
select * from dummy 
where exists (select col3 from unq where dummy.col3 = unq.col3);

DBFiddle demo

Cetin Basoz0
Related