###Quick Intro
Looker natively translates a type: count
measure into a COUNT(*)
. The sql:
parameter has no effect on the produced SQL, which means that the below measure will still produce a COUNT(*)
.
- measure: count
type: count
sql: ${name}
I’ve been approached with expectations that the above would produce a COUNT(name)
, which would count the rows of non-null name values.
###The Looker Way
I want to write the following SQL statement in Looker to count all the rows with non-null name values:
SELECT COUNT(name)
FROM users
The way to replicate this in LookML terms is by filtering a type: count
measure on the LookML field’s NOT NULL
equivalent. To do this, use the filters
parameter.
- measure: name_count
type: count
filters:
name: '-NULL'
For more information on filter expression syntax please see our doc here.
NOTE:
NOT NULL
is for integers and-'NULL'
is for strings.
The SQL produced by the above measure is as follows:
SELECT
COUNT(CASE WHEN NOT (users.name IS NULL) THEN 1 ELSE NULL END) AS `users.name_count`
FROM users
LIMIT 500