Postgres Distinct or Group by

Question

I have a table with field bhk, size, price. i am using distinct to get unique by following query

1 Query

select distinct(bhk,size,perprice),bhk,size,price from project_units;

and i and also querying by

2 Query


select bhk, array_agg(size) as size from project_units where project_id = '12' and bhk is not null and not bhk = '1bhk' group by bhk

as a result i get

[
{
bhk:"1bhk",
size:{123,121,231}
},
{
bhk:"2bhk",
size:{223,321,131}
}


]

By 2 Query i also want to retrieve price also or there is any other way to get distinct on bhk size and price by 2 Query

distinct is not a function. It always applies to all columns in the select list. Enclosing one (or more) of the columns with parentheses won't change anything and is useless. distinct (a),b is the same as distinct a,(b) or distinct a,b — user330315
– user330315, Commented Oct 15, 2019 at 12:55
But with multiple columns in the parens, you get a row constructor, so distinct (a,b),c is the same as distinct row(a,b),c — jjanes
– jjanes, Commented Oct 15, 2019 at 14:18

MMV · Accepted Answer · 2019-10-15 12:53:01Z

1

and what price would you want there? as in the queries you write need to fit all data, not just for a given set. So let's imagine you have a

bkh='1bhk'
size='123'
price='1'

and

bhk='1bhk'
size='321'
price=2

so for a distinct bhk there are 2 possible prices.

If you know which price you want (min, max, average, sum) then you can add it to the query - just it needs to be a group expression

answered Oct 15, 2019 at 12:53

MMV

9906 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1