Menu
I have such a table
id SERIAL,
user_id INT,
community_id INT[],
The table filled this way:
id | user_id | community_id
1 | 1 | {2, 4}
2 | 5 | {2, 5}
3 | 10 | {2, 4}
I'd like to get COUNT of users which each community has, community_id is array cuz user can be in several community at the same time.
The query should be simple as:
SELECT community_id, COUNT(user_id) FROM tbl GROUP BY community_id
The result should be like this:
community_id | user_count
2 | 3
4 | 2
5 | 1
I do not know how to GROUP BY array column. Can anybody help me ?
390
We access array elements using the subscript within square brackets [] . By default, PostgreSQL uses one-based numbering for array elements. It means the first array element starts with number 1.
To define a new aggregate function, one selects a data type for the state value, an initial value for the state, and a state transition function. The state transition function takes the previous state value and the aggregate's input value(s) for the current row, and returns a new state value.
The PostgreSQL GROUP BY clause is used to divide rows returned by SELECT statement into different groups. The speciality of GROUP BY clause is that one can use Functions like SUM() to calculate the sum of items or COUNT() to get the total number of items in the groups.
The purpose of unnest function in PostgreSQL is to expand the array into rows. Unnest function generates a table structure of an array in PostgreSQL. Unnest array function is beneficial in PostgreSQL for expanding the array into the set of values or converting the array into the structure of the rows.
You can use unnest() to get a normalized view on the data, and the aggregate:
select community_id, count(*)
from (
select unnest(community_id) as community_id
from tbl
) t
group by community_id
order by community_id;
But you should really fix your data model.
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
