By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (
id INTEGER,
info VARCHAR(3)
);
INSERT INTO tablename (id,info) VALUES
(1,'no'),
(1,'yes'),
(2,'yes'),
(3,'yes'),
(4,'yes'),
(4,'no'),
(5,'yes'),
(6,'yes'),
(6,'yes'),
(6,'yes'),
(6,'no'),
(7,'yes'),
(8,'no');
13 rows affected
SELECT DISTINCT
SUM(MAX(CASE WHEN info = 'yes' THEN 1 ELSE 0 END)) OVER () id_as_yes,
COUNT(CASE WHEN COUNT(DISTINCT info) = 2 THEN 1 END) OVER () id_as_yes_no
FROM tablename
GROUP BY id
| id_as_yes | id_as_yes_no |
|---|---|
| 7 | 3 |
Warning: Null value is eliminated by an aggregate or other SET operation.