Alex Rivera | Logout

Create array in SELECT

Asked 2012-03-20T22:39:09.937
11

I'm using PostgreSQL 9.1 and I have this data structure:

A     B
-------
1     a
1     a
1     b
1     c
1     c
1     c
1     d
2     e
2     e

I need a query that produces this result:

1    4     {{c,3},{a,2},{b,1},{d,1}}
2    1     {{e,2}}

A=1, 4 rows total with A=1, the partial counts (3 rows with c value, 2 rows with a value, .....)

  • The distinct values of column "A"
  • The count of all rows related to the "A" value
  • An array contains all the elements related to the "A" value and the relative count of itself

The sort needed for the array is based of the count of each group (like the example 3,2,1,1).

Edit
Report

1 Answer

6

Maybe I'm missing something, but this should do it:

SELECT a, 
       count(*) as cnt,
       array_agg(b) as all_values
FROM your_table
GROUP BY a
answered 2012-03-20T22:41:57.470

Your Answer