Create a SQL 'HAVING' clause

Hi @scottburton11!

Yes - it’s totally possible by applying a filter to the measure itself. For example in this look I’ve applied a filter is greater than 20 to the count measure. This in turn generated the following SQL:

SELECT 

	COUNT(*) AS `orders.count`

FROM demo_db.orders  AS orders



HAVING 

	(COUNT(*) > 20)

hi @aleks! is it possible to make this work directly in a measure or dimension? basically looking to have a boolean that tells me if a user has orders.count > 20 then yes else no. But need to keep it in the model, not as a filter, as I will add other conditions.

The only way I could find is this: https://help.looker.com/hc/en-us/articles/360041919653-How-to-Dimensionalize-a-Measure-in-Looker

The real problem comes up when ones want to use a non-aggregate measure to lookup something, i.e. a GROUP_CONCAT from an derived table that normalizes a multi-value field stored in a single database column using a json array:

 measure: measure_1 {

     type: string

     sql:  ( SELECT GROUP_CONCAT( t.name SEPARATOR ';' )

              FROM ${normalized_derived_table.SQL_TABLE_NAME} t

             WHERE t.ref_id = ${id} ) ;;

   }

When a Look is created filtering by the measure to get the records with a not null in the measure, the code that Looker generates is something like this:

 SELECT

 	table_base.id  AS `table_base.id`,

 	( SELECT GROUP_CONCAT( t.name SEPARATOR ';' )

             FROM looker_scratch.normalized_derived_table t

             WHERE t.ref_id = table_base_ext.id )  AS `table_base_ext.measure_1`

 FROM table_base  AS table_base

 JOIN table_base_ext AS table_base_ext ON table_base.id = table_base_ext.id

 WHERE



     TRUE

 GROUP BY 1

 HAVING

 	(( SELECT GROUP_CONCAT( t.name SEPARATOR ';' )

             FROM looker_scratch.normalized_derived_table t

             WHERE t.ref_id = table_base_ext.id ) IS NOT NULL )

The problem is that in the last line of the above SQL, the table_base_ext.id into the HAVING clause causes a SQL error in the query parser and the query does not run.

The ideal code would be something like this:

 SELECT

 	table_base.id  AS `table_base.id`,

 	( SELECT GROUP_CONCAT( t.name SEPARATOR ';' )

             FROM looker_scratch.normalized_derived_table t

             WHERE t.ref_id = table_base_ext.id )  AS `table_base_ext.measure_1`

 FROM table_base  AS table_base

 JOIN table_base_ext AS table_base_ext ON table_base.id = table_base_ext.id

 WHERE



     TRUE

 GROUP BY 1

 HAVING

 	(table_base_ext.measure_1 IS NOT NULL )

This would run without problems, but I can not find any work-around to make Looker to generates that.

Any ideas?

A non wanted work-around is to use a dimension instead of a measure, because then the IS NOT NULLwil be moved from the HAVING to the WHERE, the SQL query will run, but the performance will be worst .

A other non wanted work-around is the article mentioned in a post above about how to dimensionalize a measure using a NDT, because this creates a temporary table with the result of the GROUP_CONCAT that can be enough big to cause problems in the database server and stuk the query in “sending data” state ( MySql ) for several minutes.

May be in a further Looker version the code generated into HAVINGclauses can be improved, right now I am using Looker v7.

Unfortunately, there’s no way to do that as we can’t use Measures in Custom Filter , and that would be the ideal solution here. I do see a limitation, however, when you would use a dimension and a measure at the same time in Custom Filter. Potentially it would be either invalid (one has to go to WHERE another to HAVING) or the engine behind it would have to be sophisticated enough to deal with that. 

Having OR between dimension or a measure would be the worst-case scenario, but that could be disallowed somehow..