1

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

0
4

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)
5
  • I really like this solution! It's easily extensible to many columns too. – Colin 't Hart Mar 25 at 6:19
  • 1
    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
1

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

2
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.