Question

Create a SQL 'HAVING' clause

  • 8 May 2017
  • 8 replies
  • 1162 views

Hi!


Is there a way to insert a SQL HAVING clause?


SELECT 
COUNT(*) AS count,
source
FROM
events
GROUP BY 2
HAVING COUNT(*) > 20
ORDER BY 1

I haven’t found it in any obvious place. Custom filters don’t have access to groups, and measure filters don’t have a sql clause or anything.


Thanks!


8 replies

Userlevel 2

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)

Ah duh, again I’ve missed the obvious.


Thanks Aleks!

Userlevel 3
Badge

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

I think you can write something like this into your LookML for that view:


measure: special_filter
type: number
sql: case when count(distinct(order_id)) > 20 then 1 else 0 end;;

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.

Hi,

 

Is it possible to do a HAVING clause with two separate measure when either one needs to be satisfied? Essentially, I need to do a HAVING clause with OR on two measures replicating what is below in an NDT.

I can get this into an SQL derived NDT, but ideally I would like it into a Looker derived NDT. I’m just not quite sure to put into the LookML to do an OR function. When I try to generate it within the Explore and apply the respective filters, it returns the filters in the HAVING clause but it is an AND function:

 

SELECT
fabd.account_bonus_nk
FROM
omni.fact_table fabd
WHERE
fabd.date_sk >= 20200101
AND fabd.date_sk < (TO_CHAR(SYSDATE, 'YYYYMMDD'))::INTEGER
GROUP BY
1
HAVING
(
CASE
WHEN SUM(fabd.volume_placed-fabd.cancelled_placed_amount) > SUM(fabd.total_bonus_issued)
THEN SUM(fabd.total_bonus_issued)
ELSE SUM(fabd.volume_placed-fabd.cancelled_placed_amount)
END) > 0
OR SUM(fabd.bonus_gross_revenue) <> 0 )

 

Userlevel 7
Badge +1

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

Userlevel 4

You could use a `sql_always_having` (docs). Of course, that is an explore level parameter, so if you want to use the explore in other contexts (where the having clause is not wanted) you’d need to use Liquid. `is_selected` and `in_query` would be your friends.

Reply