-1

This seems like a rather straightforward problem, yet I have not been able to find the solution:

In a table test, I have some subset of columns which I am interested in, say a,b,c,d,e,f.

Some or most of these columns are NULL, but at least one is always filled.

Now for some rows, returned, say by:

SELECT rowid,a,b,c,d,e,f LIMIT 1;

I would like to get the number of rows which have the same non-null values.
So for example if a,d,f are the columns that are not NULL for this row, the result would be the same as for:

SELECT COUNT(*) 
FROM test WHERE a=a_ AND d=d_ AND f=f_ 

SELECT a as a_, d as d_, f as f_ FROM test LIMIT 1;

How can this be done in one step / line? Or do I need a temporary table?

forpas
  • 160,666
  • 10
  • 38
  • 76
Radio Controlled
  • 825
  • 8
  • 23

1 Answers1

1

You can use the operator IS to compare safely values that may be NULL:

SELECT COUNT(*) 
FROM test t1
INNER JOIN (SELECT a, b, c, d, e, f FROM test LIMIT 1) t2
ON (t1.a, t1.b, t1.c, t1.d, t1.e, t1.f) IS (t2.a, t2.b, t2.c, t2.d, t2.e, t2.f);

or with a CTE:

WITH cte AS (SELECT a, b, c, d, e, f FROM test LIMIT 1)
SELECT COUNT(*) 
FROM test t1 INNER JOIN cte t2
ON (t1.a, t1.b, t1.c, t1.d, t1.e, t1.f) IS (t2.a, t2.b, t2.c, t2.d, t2.e, t2.f);

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76