How to do a Supabase "Group By" Query

The Supabase JavaScript SDK library does not have a .groupBy() method. Lucky for us, Supabase gives us the full power of our Postgres database with the SQL Editor right in the dashboard. We can create a SQL view and treat it as a Table that can be queried from the Supabase JS like any other table.

To create a view in PostgreSQL that performs a GROUP BY query, you can use the CREATE VIEW statement along with a SELECT statement that includes a GROUP BY clause.

Here's an example of how you might create a view in PostgreSQL to perform a GROUP BY query:

CREATE VIEW population_by_country AS
SELECT country, SUM(population) as total_population
FROM countries
GROUP BY country;

This view will group the rows in the countries table by the country column, and will calculate the sum of the population values for each group. You can then query the view just like any other table in the database:

const { data, error } = await supabase.from('population_by_country').select('country')

The above query with the Supabase JS SDK is equivalent to the following SQL Query on the same View.

SELECT * FROM population_by_country;

You can also use the HAVING clause in your SELECT statement to filter the results of the GROUP BY query based on the values of the aggregated columns:

CREATE VIEW population_by_country AS
SELECT country, SUM(population) as total_population
FROM countries
GROUP BY country
HAVING total_population > 100000000;

This view will group the rows in the countries table by the country column, calculate the sum of the population values for each group, and filter the resulting rows to include only those where the total_population is greater than 100,000,000.

Until Supabase adds a .groupBy() method to the Supabase JS library, creating a SQL View with the GROUP BY clause already applied is the best way to perform a Supabase Group By query.