count only non-NULL rows in PRQL

[richb-hanover]

From #4384: It appears that count always compiles to COUNT(*), which always includes NULL rows. Is there a way to "count only non-NULL rows" in PRQL? That would be equivalent to SQL's COUNT(foo)... (I didn't see anything in the Book that seemed to do this.) Thanks.

[eitsupi]

You want something like this?

from bar
filter foo != null
select {count this}

[richb-hanover]

Sorry for the delayed response: I wanted to think carefully about what I was asking... This question is an outgrowth of #4384 (count with filter). I wanted to get a count of the total number of rows, plus a count of rows that fulfilled certain criteria. @snth is correct: there is a simple fix: derive a column that is set to 1 for rows that satisfy; 0 otherwise.

But here I'm asking a bit of a "language feature" question: if I already have a column that contains values or NULLs, is there a way to do it in one step (without the derive)? I am thinking it might invoke SQL's COUNT(FOO) instead of COUNT(*)

For example, here is a query on sample data. The Playground shows the result of the SQL query as [2, 2, 2].

from_text format:json """
{
    "columns": ["a", "b"],
    "data": [
        [1, "x"],
        [4, null]
    ]
}
"""

aggregate {
  c = count this,
  d = count b,
  e = count b,
}

But if I manually change the generated SQL to end with COUNT(b) AS e and run it directly against SQLite, the generated results are [2, 2, 1] (my desired result)

Would this be a useful feature? That is, distinguish between count this that applies to the entire relation and count foo that counts non-NULL in a column? Many thanks.

[kgutwin]

If you're looking for a quick fix, an S-string will do it fairly easily:

aggregate {
  c = count this,
  d = count b,
  e = s"count({b})",
}

generates the desired SQL:

WITH table_0 AS (
  SELECT
    1 AS a,
    'x' AS b
  UNION
  ALL
  SELECT
    4 AS a,
    NULL AS b
)
SELECT
  COUNT(*) AS c,
  COUNT(*) AS d,
  count(b) AS e
FROM
  table_0

I agree, though, having some sort of mechanism to add a filter to the count context would be quite useful. I already have had to put together a set of S-strings for counting distinct values, true values, and non-null values...

[richb-hanover]

@kgutwin That's a good solution. I see this could be made into a function let count_non_null = x -> s'count ({x})' and could be used as e = count_non_null b.

If they are not too special purpose, it would be terrific if you would share your other S-strings. We can collect them as a series of "pro tips". Thanks!

[kgutwin]

Sure, here's a quick example that runs in the playground. I forgot that count_distinct is already in the std lib, so there's just two counting-based S-strings:

let count_non_null = x -> s"count({x}) filter ({x} is not NULL)"
let count_true = x -> s"count({x}) filter ({x})"

from_text format:json """
{
    "columns": ["a", "b"],
    "data": [
        [1, true],
        [2, null],
        [3, false],
        [4, false]
    ]
}
"""

aggregate {
  four = count this,
  three = count_non_null b,
  two = count_distinct b,
  one = count_true b
}

This syntax works for DuckDB. I don't know the syntax for other dialects...

[richb-hanover]

And here's where the differences between SQL engines will bite you... That syntax works for DuckDB, but SQLite doesn't support a filter clause after a count... Still it's useful to collect these tips for situations where they can be used. Thanks!

[richb-hanover]

A couple workarounds:

@snth notes that you can derive a column based on whether another column is NULL or not, then sum() that new column

@kgutwin suggests using an S-string in a function: let count_non_null = x -> s'count ({x})'

@kgutwin also suggests these functions count_non_null and count_true (see #4395 (reply in thread)) using S-string for SQL implementations that support filter clause after a count.