By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Create the table
CREATE TABLE MyTable (
uid INT PRIMARY KEY,
agreewith_a INT,
agreewith_b INT
);
-- Populate the table with the provided data
INSERT INTO MyTable (uid, agreewith_a, agreewith_b) VALUES (1, 10, 7);
INSERT INTO MyTable (uid, agreewith_a, agreewith_b) VALUES (2, 5, 5);
INSERT INTO MyTable (uid, agreewith_a, agreewith_b) VALUES (3, 10, 2);
3 rows affected
Select
val,
count(*),
count
(
case agreewith
when 'agreewith_a'
then 1
end
),
count
(
case agreewith
when 'agreewith_b'
then 1
end
)
from
(
select
agreewith_a,
agreewith_b
from MyTable
) t1
UNPIVOT
(
val
FOR agreewith
IN (agreewith_a, agreewith_b)
) t2
group by val
| val | (No column name) | (No column name) | (No column name) |
|---|---|---|---|
| 2 | 1 | 0 | 1 |
| 5 | 2 | 1 | 1 |
| 7 | 1 | 0 | 1 |
| 10 | 2 | 2 | 0 |
Warning: Null value is eliminated by an aggregate or other SET operation.