Create an array in SELECT

Question

I am 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 lines total with A = 1, partial calculations (3 lines with value c, 2 lines with value, .....)

• Different values ​​of column "A"
• The number of all rows related to the value "A"
• The array contains all the elements related to the value "A" and the relative number of the

The variety needed for the array is based on counting each group (for example, example 3,2,1,1).

Answer 1

This should do the trick:

SELECT a
     , sum(ab_ct)::int AS ct_total
     , count(*)::int   AS ct_distinct_b
     , array_agg(b || ', ' || ab_ct::text) AS b_arr
FROM  (
    SELECT a, b, count(*) AS ab_ct
    FROM   tbl
    GROUP  BY a, b
    ORDER  BY a, ab_ct DESC, b  -- append "b" to break ties in the count
    ) t
GROUP  BY a
ORDER  BY ct_total DESC;

Returns:

• ct_total: total bon a.
• ct_distinct_b: b a.
• b_arr: b b, b.

b a.

ORDER BY PostgreSQL 9.0 . :

SELECT a
     , sum(ab_ct)::int AS ct_total
     , count(*)::int   AS ct_distinct_b
     , array_agg(b || ', ' || ab_ct::text ORDER BY a, ab_ct DESC, b) AS b_arr
FROM  (
    SELECT a, b, count(*) AS ab_ct
    FROM   tbl
    GROUP  BY a, b
    ) t
GROUP  BY a
ORDER  BY ct_total DESC;

, . . , . :

• ORDER BY LIMIT ?

Answer 2

, - , :

SELECT a, 
       count(*) as cnt,
       array_agg(b) as all_values
FROM your_table
GROUP BY a

Answer 3

This is what you need:

SELECT A, COUNT(*), array_agg(b)
FROM YourTable
GROUP BY A