Obviously, COUNT(DISTINCT)
with multiple columns counts unique combinations of the specified columns' values. However, one other important point is that a tuple is counted only if none of the individual values in the tuple is null. If that last aspect of the behaviour is what you are trying to achieve, you could emulate it using a conditional inside COUNT
. It could be either the standard-compliant CASE
:
SELECT
COUNT(CASE WHEN col1 IS NOT NULL AND col2 IS NOT NULL THEN 1 END)
FROM
demo
;
or the MySQL-specific IF
function:
SELECT
COUNT(IF(col1 IS NOT NULL AND col2 IS NOT NULL, 1, NULL))
FROM
demo
;
where instead of the 1
you can put any non-null constant. A row will be counted only if neither col1
nor col2
is null.
The obvious flaw of this workaround (either variation) is that it is clearly rather unwieldy and will become ridiculously long very quickly as you add more columns to account for.
It is possible to shorten the expression somewhat by choosing a less clear syntax. In particular, you could replace the COUNT
with SUM
and treat the predicates as numbers (1/0) in an arithmetic expression:
SELECT
SUM( (col1 IS NOT NULL) * (col2 IS NOT NULL) )
FROM
demo
;
In the context of the arithmetic operator *
the logical result of the IS NOT NULL
operator is implicitly converted to a number, 1 for True, 0 for False. With only 1s and 0s the *
operator works as an equivalent of the logical AND
: the final result will be 1 (True) only if each operand is 1; otherwise the result will be 0 (False). Adding up (SUM
) the 1s is equivalent to counting the truths.
So again, the above statement will count only the rows where neither col1
nor col2
is null.