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?
>Solution :
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.