"having" statement in aggregate functions

mike-k

I'm doing a group by to aggregate a table and get a count of record. I'd like to only get the rows where the count = 1. In SQL, this would be something like:

select name, count(1)
from table
group by name
having count(1) = 1.

Is this possible using the query all record function? I don't see it in the output tab.

Ray Deck

It's not part of the query all records, but you could apply that filter pretty straightforwardly in the function stack following the query. I would use the array function, "find every element" and set up criteria to apply the "having" logic you've articulated above.

Under the covers Xano uses a Postgresql database so this functionality is just a step away, but they don't seem to surface it directly yet - so we use workarounds like the above.

We often work on data manipulation challenges as part of our focus on the hardest 5% of challenges in our daily office hours and loom-enabled help discussions at Statechange.ai.

mike-k

Thanks, Ray! Your suggestion is essentially what I did, was just hoping for something more efficient.

Chris Coleman

Hey Michael, great suggestion. I've added this to our feature request board here; please give it an upvote if you have time!