Group By A Column And Display The Value Of The Column That Matches The Condition

I have to GROUP BY a column and if there are more than one entries for it, I need to display the one that satisfies the condition. If only one entry is there it should be displayed

Solution 1:

With this:

select 
  name,
  case 
    when count(distinct groupid) = 1 then max(groupid)
    when sum(case when groupid = 'y' then 1 end) > 0 then 'y'
    else 'x'
  end groupid   
from tablename
group by name

For:

CREATE TABLE tablename (
    id  INTEGER PRIMARY KEY AUTOINCREMENT,
    name    TEXT,
    groupid TEXT
);
INSERT INTO tablename (id,name,groupid) VALUES 
 (1,'A','x'),
 (2,'A','y'),
 (3,'B','x'),
 (4,'C','z'),
 (5,'A','z'),
 (6,'B','y'),
 (7,'D','k'),
 (8,'D','m');

The results are:

| name | groupid |
| ---- | ------- |
| A    | y       |
| B    | y       |
| C    | z       |
| D    | x       |

See the demo.

Solution 2:

You describe:

select t.name,
       (case when count(*) > 2 then 'y'
             else max(groupid)
        end)
from t;

But I think you really want:

select t.name,
       (case when min(groupid) <> max(groupid)  then 'y'
             else max(groupid)
        end)
from t;

Solution 3:

You can try below -

select name, case when count(groupid)>2 then 'y' else min(groupid) end as groupid
from tablename a
group by name