Group by PostgresSQL

Question

I have the following result

Student Form ID  | Result
-----------------------------
12               | PASSED
131              | PASSED
131              | RESIT
144              | FAIL
23               | NA

Ideally I want where the ID is the same eg 131. To group the results so ID Result

Student Form ID  | Result
--------------------------------
131              | PASSED, RESIT

When I use group by, it isn't giving me the desired output, I'm obviously doing it wrong. Please could I have some guidance on how to achieve my desired result, i.e. where the ID is the same, the results should be grouped on one row.

Try using `string_agg` or `array_agg` to aggregate the results. — Laurenz Albe, Apr 09 '19 at 07:59
`SELECT id, STRING_AGG(result, ', ') AS result FROM your_table GROUP BY id` — 404, Apr 09 '19 at 08:10

score 0 · Answer 1 · answered Apr 09 '19 at 08:11

0

You can use aggregation is PostgreSQL: array_agg() or json_agg()

select student_form_id, array_agg(result) from your_table group by student_form_id;

answered Apr 09 '19 at 08:11

Gaurav Neema

146
1
1
12

Correct but why mention json_agg, there is no json in this question. – 404 Apr 09 '19 at 08:12
It's safe to use json_agg() in my opinion. Output can be processed easily. – Gaurav Neema Apr 09 '19 at 08:18
Mavis is clearly looking for `string_agg()` – Apr 09 '19 at 08:23
Thank you everyone for all the help, string_agg worked perfectly. Thanks again :) – Mavis Banda Apr 09 '19 at 08:26

Group by PostgresSQL

1 Answers1