count with filter?

[richb-hanover]

I derive a field in my table with a case statement that sets it to "Too big" or "Too small". I want to summarize and produce a count of all rows, plus a count of the rows with "Too small". (Alternatively, I could just embed the filter criteria in the count without the addition of an extra column...) (that won't work...)

What's the "PRQL Way" to do this? Thanks.

Solution: derive a column with a value of 1 for "Too small" and 0 otherwise; use sum to count those rows, and count this for the full set.

[snth]

Something like the following?

from [{a=0.1}, {a=0.2}, {a=0.3}, {a=1}, {a=2}, {a=3}]
derive b = case [a < 1 => 0, true => a]
group b (
  aggregate {c = count this}
  )

I've just used "0" instead of "Too small".

Also, ignore the odd factor of 10 in the values of b, I've logged that in #4385.

[richb-hanover]

Sorry, that's not quite what I meant... I have described the problem badly.

I have a table with three fields: ZoningDistrict, MinimumLotSize, and ActualLotSize. (Many lots in a zoning district may be smaller than the "legally allowed minimum" because they are grandfathered.) I want to group by District, and have a result that shows:

• District
• Number of total rows in that district
• Number of "small lots" where ActualLotSize < MinimumLotSize

The result might look like:

ZoningDistrict	NumberOfLots	NumberSmallLots
Central	107	37
Rural	209	45

I see how to use a case to derive a new field that is essentially a boolean ("too small"). How can I aggregate/count both the number of rows in a group (count this) and also the number of rows that are "too small"? Thanks.

[snth]

How about this?

from [{a=0.1, d=1}, {a=0.2, d=1}, {a=0.3, d=2}, {a=1, d=1}, {a=2, d=2}, {a=3, d=3}]
derive b = case [a < 1 => 1, true => 0]
group d (
  aggregate {count_ = count this, too_small = sum b}
  )

Or in one go:

from [{a=0.1, d=1}, {a=0.2, d=1}, {a=0.3, d=2}, {a=1, d=1}, {a=2, d=2}, {a=3, d=3}]
group d (
  aggregate {
    count_ = count this,
    too_small = sum case [a < 1 => 1, true => 0]
  }
)

[richb-hanover]

Yes, indeed! sum does the trick.

For my future information: 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? (Equivalent to SQL's COUNT(foo)...)Thanks.