Creating a type count measure for non-primary keys

[Zam]

###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