Custom database queries

Georges Duverger

I have a large table (1M+ records). I'd like to analyze it. Nothing too sophisticated but something like:
select
distinct(foo),
count(bar) as count
from
table
group by
foo
order by
count descI only see a way to define custom `where` clauses in the product. If it's not possible, I can always take the data out and figure it out but I was wondering if I was maybe missing a way to achieve something like that in Xano.

Cheers.

Michael Udinski

Hey 

You can use the Query All Records function to accomplish this...

You'd change the return type to aggregate

[CleanShot 2021-09-02 at 15.40.29.png]
This will give you access to Group By, Aggregate By, and Sort By... In Aggregate By you will have access to different aggregates. For your query it sounds like you will want to use "count_distinct"

[CleanShot 2021-09-02 at 15.41.56.png]
You can still use the By Custom Query 'where' statements to do any pre-filtering that you may need. 

Georges Duverger

 Oh, wow! Even time I think I'm pushing the limit, you folks already have a solution for it. I can't wait to give it a try later. Thanks for the quick response. 💪

Michael Udinski

Let me know if you run into any issues

Sean Montgomery

If performance starts to get slow, you may want to add an index on anything you are filtering by. If it is a pure global aggregation then it is what it is, but if you are trying to do stats for A=B, then adding an index on A would probably speed things up.

Indexes can be added in the spreadsheet view.

Mr_English

  Just implemented a Grouped By and Aggregated By. So quick and straightforward. Thanks!