SQL table column values to select query list

Question

So I have a table that has EMAIL and Order ID.

EMAIL   |   id
--------------
[email protected] |   1
[email protected] |   2
[email protected] |   3

And I need to SELECT it so that I'd have email column that is distinct and ids column that is array of int's

EMAIL   |   ids
--------------
[email protected] |   [1,2]
[email protected] |   [3]

I use PSQL 9.3. I looked at aggregate functions, but since I'm not too good with SQL atm, I didn't really understand them. My code so far is:

SELECT DISTINCT ON (email) email
FROM order
WHERE date > '2013-01-01';

Erwin Brandstetter · Accepted Answer · 2014-12-18 15:44:53Z

3

Use the aggregate function array_agg():

SELECT email, array_agg(id) AS id_array
FROM   "order"
WHERE  date > '2013-01-01'
GROUP  BY email;

Aside: your identifiers ...

Don't use order as table name, it's a reserved word.
Don't use date as column name, it's a reserved word in standard SQL and a basic type name in Postgres.
I wouldn't use id as column name either, that's a common anti-pattern, but "id" is not a descriptive name. Once you join a couple of tables you have n columns named "id" and you need to start dealing out column aliases, not to speak of the confusion it may cause.

Instead, use something like this:

CREATE TABLE order_data (
   order_data_id serial PRIMARY KEY
 , email         text
 , order_date    date
);

edited Dec 18, 2014 at 15:44

answered Dec 18, 2014 at 14:39

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

Mihkel L. Over a year ago

urr.. I was so sure that I tryed that array_agg(id). And thanks for pointers in style. :)

Collectives™ on Stack Overflow

SQL table column values to select query list

1 Answer 1

Aside: your identifiers ...

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Aside: your identifiers ...

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related