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
Post a Comment for "Group By A Column And Display The Value Of The Column That Matches The Condition"