How to aggregate values into an array in PostgreSQL?

Question

I have a two tables

Student
--------
Id  Name
1   John    
2   David
3   Will

Grade
---------
Student_id  Mark
1           A
2           B
2           B+
3           C
3           A

Is it possible to make native Postgresql SELECT to get results like below:

Name    Array of marks
-----------------------
'John',     {'A'}
'David',    {'B','B+'}
'Will',     {'C','A'}

But not like below

Name    Mark
----------------
'John',     'A'
'David',    'B'
'David',    'B+'
'Will',     'C'
'Will',     'A'

Michael Buen · Accepted Answer · 2012-06-13 23:18:28Z

264

Use array_agg: http://www.sqlfiddle.com/#!1/5099e/1

SELECT s.name,  array_agg(g.Mark) as marks        
FROM student s
LEFT JOIN Grade g ON g.Student_id = s.Id
GROUP BY s.Id

By the way, if you are using Postgres 9.1, you don't need to repeat the columns on SELECT to GROUP BY, e.g. you don't need to repeat the student name on GROUP BY. You can merely GROUP BY on primary key. If you remove the primary key on student, you need to repeat the student name on GROUP BY.

CREATE TABLE grade
    (Student_id int, Mark varchar(2));

INSERT INTO grade
    (Student_id, Mark)
VALUES
    (1, 'A'),
    (2, 'B'),
    (2, 'B+'),
    (3, 'C'),
    (3, 'A');


CREATE TABLE student
    (Id int primary key, Name varchar(5));

INSERT INTO student
    (Id, Name)
VALUES
    (1, 'John'),
    (2, 'David'),
    (3, 'Will');

edited Jun 13, 2012 at 23:18

answered Jun 7, 2012 at 8:56

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

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

2 Comments

mrbrdo Over a year ago

Oh my god thank you so much about your remark about select/group, that is so awesome! That was really annoying!

FlyingV Over a year ago

stackoverflow.com/questions/38836831/…

Arion · Accepted Answer · 2012-06-07 08:40:52Z

13

What I understand you can do something like this:

SELECT p.p_name, 
    STRING_AGG(Grade.Mark, ',' ORDER BY Grade.Mark) As marks
FROM Student
LEFT JOIN Grade ON Grade.Student_id = Student.Id
GROUP BY Student.Name;

EDIT

I am not sure. But maybe something like this then:

SELECT p.p_name, 
    array_to_string(ARRAY_AGG(Grade.Mark),';') As marks
FROM Student
LEFT JOIN Grade ON Grade.Student_id = Student.Id
GROUP BY Student.Name;

Reference here

edited Jun 7, 2012 at 8:40

answered Jun 7, 2012 at 8:34

Arion

31.3k10 gold badges74 silver badges89 bronze badges

1 Comment

ThiefMaster Over a year ago

I think he wants a pgsql array, not a comma-separated string

Javi · Accepted Answer · 2023-06-23 09:21:23Z

11

Michael Buen got it right. I got what I needed using array_agg.

Here just a basic query example in case it helps someone:

SELECT directory, ARRAY_AGG(file_name) 
FROM table 
WHERE type = 'ZIP' 
GROUP BY directory;

And the result was something like:

|     parent_directory    |                 array_agg              |  
+-------------------------+----------------------------------------+
| /home/postgresql/files  | {zip_1.zip,zip_2.zip,zip_3.zip}        |
| /home/postgresql/files2 | {file1.zip,file2.zip}                  |

This post also helped me a lot: "Group By" in SQL and Python Pandas. It basically says that it is more efficient to use only PSQL when possible, but that Python Pandas can be useful to achieve extra functionalities in the filtering process.

edited Jun 23, 2023 at 9:21

answered Feb 28, 2019 at 9:43

Javi

1,2412 gold badges19 silver badges21 bronze badges

Comments

niko · Accepted Answer · 2012-06-07 08:55:57Z

9

You could use the following:

SELECT Student.Name as Name,
       (SELECT array(SELECT Mark FROM Grade WHERE Grade.Student_id = Student.Id))
       AS ArrayOfMarks 
FROM Student

As described here: http://www.mkyong.com/database/convert-subquery-result-to-array/

edited Jun 7, 2012 at 8:55

answered Jun 7, 2012 at 8:25

niko

1,86613 silver badges13 bronze badges

1 Comment

Blue Smith Over a year ago

Thanks a lot, array function is much faster than array_agg, especially, when there are millions of records.

Collectives™ on Stack Overflow

How to aggregate values into an array in PostgreSQL?

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related