Left Join an Aggregated Query

Prepify

I am executing a "Query All Records" based on a userID. I am then aggregating those records with Count Distinct, and a Sum aggregation.

I want to subsequently (within the same function, or separate) left join another table where the PointFloor variable < = TotalPoints AND PointCeling > TotalPoints.

Is there any way to do this? I know you can join tables in the Filter By Joins section. But that only looks to work if you are joining pre-aggregation (unless I am missing something).

Sam

This required a HAVING in your query (raw query) based on the agg function. Afaik you can't achieve that with the query builder xano offer, better to do it in a new "Direct db query". It let you write raw query to db.

Prepify

@Sam This would not be a HAVING situation because the join is happening after the aggregation, not before. It would be a left join on a aggregated subquery with the join having multiple "ON" criteria. In any case, direct queries are only available in the upper tiers of the paid plan (which I do not have).

MattLaro

Not having access to subqueries pretty much answers your question. There's no way to do this in one shot through one DB query without using the direct database query. Everything aggregated is only accessed after the data has been compiled.

The only workaround I found at the time is to insert aggregated data in another table (like a materialized view in a DB perspective) linked 1-1 on the table of interest… either that or create columns with the aggregated data that cannot be updated externally, but I would absolutely not recommend this as this is FAR from ideal. It requires all your insert and/or update data operations on a table to be strictly funneled into 1 function and then you have to make sure these calculations are made each time.