# count distinct values over columns

I struggle to get the result for

here is my dataset

A B C cnt
2 1 2
1 5 5
2 1 4
1 1 1

result expected

A B C cnt
2 1 2 2
1 5 5 2
2 1 4 3
1 1 1 1

PG 9.5

## 2 Answers

A correlated subquery against a `VALUES` table should do the trick:

``````SELECT
t.A, t.B, t.C,
(SELECT COUNT(DISTINCT v.n) FROM (VALUES(t.A),(t.B),(t.C)) AS v(n)) AS cnt
FROM table t
``````

If you wanted separate calculations on the values such as both counting and summing, you could use a lateral join (a.k.a `APPLY`) over `VALUES` instead:

``````SELECT A, B, C ,D, E, v.countDistinct, v.countNotNull, v.Total
FROM  test,
LATERAL (
SELECT COUNT(DISTINCT v.n), COUNT(v.n), SUM(v.n)
FROM (VALUES (A),(B),(C), (D), (E)) AS v(n)
) AS v(countDistinct, countNotNull, Total)
``````
• I really like this solution! It's easily extensible to many columns too. – Colin 't Hart Mar 25 at 6:19
• If you want multiple counts then a lateral join may be better – Charlieface Mar 25 at 6:24
• @Charlieface, like this ? SELECT t.A, t.B, t.C ,t.D, t.E, cnt FROM table t, lateral (SELECT COUNT(DISTINCT v.n) FROM (VALUES (t.A),(t.B),(t.C), (t.D), (t.E)) AS v(n)) AS cnt I get cnt::record can we avoid it ? link – FranckT Mar 25 at 10:09
• You should have written `AS v(cnt)` at the end of that. No, I thought you meant different counting or summing like this: `SELECT A, B, C ,D, E, v.cntDistinct, v.cntNotNull, v.sm FROM test, lateral (SELECT COUNT(DISTINCT v.n), COUNT(v.n), SUM(v.n) FROM (VALUES (A),(B),(C), (D), (E)) AS v(n)) AS v(cntDistinct, cntNotNull, sm)` see dbfiddle.uk/… – Charlieface Mar 25 at 12:03
• Indeed, excellent job and thank you for detailing your answer. – FranckT Mar 25 at 12:22

You can use nested CASE WHEN structure

``````CREATE TABLE test (
"A" INTEGER,
"B" INTEGER,
"C" INTEGER,
"cnt" INTEGER
);

INSERT INTO test
("A", "B", "C", "cnt")
VALUES
('2', '1', '2', '0'),
('1', '5', '5', '0'),
('2', '1', '4', '0'),
('1', '1', '1', '0');
``````
``````UPDATE test
SET cnt =
CASE  WHEN "A" = "B" THEN
CASE
WHEN "A" = "C" THEN 1
ELSE
2
END
ELSE
CASE
WHEN "A" = "C" THEN 2
WHEN "B" = "C" THEN 2
ELSE
3
END
END
``````
``````SELECT * FROM test
``````
``` A |  B |  C | cnt
-: | -: | -: | --:
2 |  1 |  2 |   2
1 |  5 |  5 |   2
2 |  1 |  4 |   3
1 |  1 |  1 |   1
```

db<>fiddle here

• Why are you inserting integers as strings? – Colin 't Hart Mar 24 at 19:26
• the tools i used makes this, and Postgres doesn't mind, so i let it stand. – nbk Mar 24 at 19:37