final cog

Today, while working for ApplianSys, I needed to count how many times an expression was True in an aggregate query.

My query began...

SELECT MAX(reply_size), COUNT(*), req_method, req_scheme, req_hostname, req_port, req_path, req_query, req_fragment, COUNT(cache_status LIKE '%HIT%')

Sqlite3 told me that all of the records were hits (as I misinterpreted the results), which was incorrect.

So, in desperation, I looked at the documentation. It didn't even mention the possibility of using an expression - COUNT was just for counting non-NULL columns.

Taking a trick from Python, I tried COUNT(cache_status LIKE '%HIT%' OR NULL) and it worked :-)

COUNT counts how many rows satisfy the condition that the expression is not NULL. Being False doesn't count (or rather it does). The short circuiting OR causes the expression to evaluate to NULL whenever the first part is False. Hence, only the True expressions are counted.