Create array in SELECT
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
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 count ofb
pera
.ct_distinct_b
: count of distinctb
pera
.b_arr
: array ofb
plus frequency ofb
, sorted by frequency ofb
.
Ordered by total count of b
per a
.
Alternatively, you can use an ORDER BY
clause within the aggregate call in PostgreSQL 9.0 or later. Like:
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;
May be clearer. But it's typically slower. And sorting rows in a subquery works for simple queries like this one. More explanation:
- How to apply ORDER BY and LIMIT in combination with an aggregate function?