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

result expected

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0