I have a filter box where people can filter by brand, model, fuel.
but before we filter in our page are at first three select boxes
Select brand, Select model, select fuel
my boss wants to show only the values that are exists in our database.
like when exists a BMW and Audi then only show in the Select brand these two brands. Not other brands.
and the same for model and fuel.
Here I have a solution how to make it for brand
select
brand
FROM cars
GROUP BY brand
but when I want to add it for model and fuel I get duplicate values maybe in brands
select
array_agg(brand) as brands,
array_agg(model) as models,
array_agg(fuel) as fuels
FROM cars
GROUP BY model, brand, fuel
I dont want to make three querys maybe I can do it in one query. Is it possible to get unique values for all three data (brands,models,fuels)?
As to
SELECT brand FROM cars GROUP BY brand
It is uncommon to GROUP BY columns and then not use any aggregation function like MIN, SUM, COUNT etc. We would rather write
SELECT DISTINCT brand FROM cars;
which makes the intention clearer. An alternative would be to select a single row with an array of the brands:
SELECT ARRAY_AGG(DISTINCT brand) FROM cars;
If you want distinct brands, models and fuels from the table, the straight-forward way would be to either write three separate queries:
SELECT DISTINCT brand FROM cars;
SELECT DISTINCT model FROM cars;
SELECT DISTINCT fuel FROM cars;
or a single query that again results in one row with an array per entity:
SELECT
ARRAY_AGG(DISTINCT brand) AS brands,
ARRAY_AGG(DISTINCT model) AS models,
ARRAY_AGG(DISTINCT fuel) AS fuels
FROM cars;