JMP Query Builder -- how to apply "HAVING" feature?

[#1]

Hello all, I have several SQL queries with "HAVING" features. I would like to see whether it can be set in JMP Query Builder. I searched over this community, but I did not see discussions on it (maybe I missed?). I understand there is one custom SQL feature that can be used to achieve this function. But the issue is in that way all nice JMP Query Builder interactive features won't be seen any more. Just wondering any comments/suggestions on setting up "HAVING" condition? Typical  SQL script is as below. Only "HAVING" condition seems not obvious to apply in the builder. Your insights will be appreciated!

SELECT column-names
  FROM table-name
 WHERE condition
 GROUP BY column-names
HAVING condition
 ORDER BY column-names

[#2]

You could replace the HAVING clause by creating your condition in a second query, joining that into your original select statement, and then filtering the result.  Here is what that might look like:

SELECT a.column-names
  FROM table-name a
LEFT JOIN (SELECT condition-group-column, summary(condition-summarized-column) as condition
  FROM table-name
  GROUP BY condition-group-column) b
  ON a.condition-column = b.condition-column
  WHERE condition > 1;

You could test this in db-fiddle using this in the schema:

CREATE TABLE test (
  id INT, val INT
);
INSERT INTO test (id, val) VALUES (1, 10);
INSERT INTO test (id, val) VALUES (2, 10);
INSERT INTO test (id, val) VALUES (3, 20);
INSERT INTO test (id, val) VALUES (4, 30);
INSERT INTO test (id, val) VALUES (5, 30);

and this in the select:

 SELECT a.ID, a.val, b.tot
  FROM test a
LEFT JOIN (SELECT val, count(id) as tot
  FROM test
  GROUP BY val) b
  ON a.val = b.val
  WHERE tot > 1;

which should look something like this:

[#3]

Thank you @ih . It helps a lot. So there is no "hidden" feature in JMP Query Builder that can accomplish "HAVING" condition directly, which is equivalent to "Filter" or "Order" button. I ran the test table you attached in this thread. Yes I could get the same query result as what you showed. I summarized 3 options in JMP to do this "HAVING" feature: 1) Using the way as @ih mentioned, run the custom query in JMP Query Builder, note that in this way no "HAVING" keyword in the query script.  2) Run the custom query, with HAVING  keyword in the script. It works for me. The custom query looks like this:

SELECT a.ID, a.val, Count (*) AS tot
FROM test a
GROUP BY a.val
HAVING ((Count (*)>1))

3) Use JMP Query Builder, but with two tables: one is "test" table, the other is the summary table of "test" table using JMP "Summary" by column ("val"). In this way, users that are not familiar with SQL scripts can join the above two tables in the builder, and use "Filter" to achieve "HAVING" condition.

Note that the last one is the initial purpose. If there are ways to avoid using the 2nd summary table, and without going into custom query, please chime in. Otherwise I think we can close this discussion. 

[#4 · Accepted solution]

Hi TG,

There is no hidden HAVING in Query Builder, sorry!  It would certainly be a consideration for an enhancement. At this time, the Custom SQL editor would be the only way of using it, and as you point out once you go that way you can't go back to interactive mode.

Brian Corcoran

JMP Development

[#5]

Thank you Brian for the update. Yes it would be nice to have this enhancement in the future versions, though not must-have for my applications as now.