CODEWITHSUNDEEP
sqlarrayspostgresqlaggregate-functions
Mihkel L.
Mihkel L.

Reputation: 1573

SQL table column values to select query list

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';

Upvotes: 3

Views: 8696

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659017

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
);

Upvotes: 3

Related Questions