# count distinct values over columns

## All we need is an easy explanation of the problem, so here it is.

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

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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)
``````

### Method 2

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂