Generating "HAVING" clauses with the query builder

Hi there! In SQL, there is a clause “HAVING” which can filter aggregated results on the same query level.

For instance, I want to see each customer and their amount of sales (say, group by customer and count sales), but I only want to see customer which made more than one purchase (that is, count sales per customers HAVING more than one sale).

Is there a way to perform that on metabase?
Of course, I can use sub queries, or saved questions, and so on, but I’m trying to achieve that considering the average non tech user.

Hi @lucas.lima
That is possible in 0.33, since you can filter on aggregated data.

Oh, thank you, flamber!
As I’m still using 0.32.10, I’ll have to wait till it becomes possible to update my version in order to get that feature. Glad to know it is already there, though - thank you!

@flamber, actually, Metabase does not generate HAVING to filter on aggregated data, instead it wraps the initial query in a subquery, and filters it with an outer WHERE.

One of the unfortunate outcomes is that such Question's fields cannot be assigned to Dashboard filters, as we discovered here. This is super sad.

Example query generated with Question builder:
SELECT source.InitiatorGroup AS InitiatorGroup, source.count AS count
FROM (SELECT v_servicedesk.InitiatorGroup AS InitiatorGroup, count(*) AS count FROM v_servicedesk
WHERE date(v_servicedesk.GroupModifiedOn) BETWEEN date(date_add(now(), INTERVAL -30 day))
AND date(now())
GROUP BY v_servicedesk.InitiatorGroup
ORDER BY v_servicedesk.InitiatorGroup ASC) source WHERE source.count > ?
LIMIT 1048576

@dtroyan Correct. It doesn’t do HAVING, but just a sub-select. But I’m fairly sure @lucas.lima was looking for a way to filter on aggregated data, not how Metabase was doing the queries underneath the hood.
I have created an issue on lack of dashboard filtering - go and upvote by clicking :+1: on the first post:
https://github.com/metabase/metabase/issues/11007

2 Likes

@flamber, thank you for the reply!

Just for the reference: using subqueries instead of HAVING clause is not the a very good practice with a number of DBMS’es (depending on their query optimizer capabilities), as this way the whole subquery results are firstly materialized in RAM (or on disk), and only then filtered. Specifying HAVING filter helps them realize that the results can be pre-filtered before materialization.

Examples are MySQL, Clickhouse.

1 Like

@dtroyan Metabase has to work with many databases, but there’s a specific request for supporting HAVING:
https://github.com/metabase/metabase/issues/1207