Create array in SELECT
20,107
Solution 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 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:
Solution 2
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
Solution 3
This is what you need:
SELECT A, COUNT(*), array_agg(b)
FROM YourTable
GROUP BY A
Author by
Deviling Master
Updated on July 02, 2020Comments
-
Deviling Master almost 4 years
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).