Postgres: get min, max, aggregate values in one select

Question

I am using Postgresql 8.4. I have a table like this:

 type | value
------+-------
 1    | 5
 2    | 6
 1    | 4
 3    | 10

I want to write one select that will give me the minimum & maximum value, and an aggregate of all the types as integer[]. The desired result should be:

 min | max | types
-----+-----+-----------
 4   | 10  | {1, 2, 3}

To get the min and max, I already have:

SELECT MIN(value) min, MAX(value) max FROM table;

To get the types in a standalone select, I use:

SELECT array_agg(DISTINCT type) types FROM table;

How can I combine these into one select (that is not too inefficient)?

Doesn't SELECT MIN(value) min, MAX(value) max, array_agg(DISTINCT type) types FROM table; work for you? — Ashalynd
– Ashalynd, Commented Mar 11, 2014 at 7:57

Hawken MacKay Rives · Accepted Answer · 2019-03-19 16:35:03Z

10

SELECT array_agg(DISTINCT type) AS types,
       MIN(value) AS min,
       MAX(value) AS max
FROM your_table

edited Mar 19, 2019 at 16:35

Hawken MacKay Rives

1,2291 gold badge17 silver badges26 bronze badges

answered Mar 11, 2014 at 7:58

Dipendu Paul

2,7631 gold badge26 silver badges23 bronze badges

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

Comments

Hamidreza · Accepted Answer · 2014-03-11 08:07:41Z

2

Try this query:

SELECT * FROM
(SELECT MIN(value),MAX(value) FROM table1)t1,
(SELECT array_agg(DISTINCT type) FROM table1)t2;

SQL Fiddle

answered Mar 11, 2014 at 8:07

Hamidreza

3,1581 gold badge20 silver badges15 bronze badges

Collectives™ on Stack Overflow

Postgres: get min, max, aggregate values in one select

2 Answers 2

Comments

SQL Fiddle

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related