I am accessing a table table that I recognised one column was defined like type list (if you come from python like I do). I retrieved its create statement via pg_dump

CREATE TABLE sensemyfeup.trips (
    trip_id integer NOT NULL,
    daily_user_id integer,
    session_ids integer[],
    seconds_start integer,
    lat_start double precision,
    lon_start double precision,
    seconds_end integer,
    lat_end double precision,
    lon_end double precision,
    distance double precision
);

I am referring to column session_ids. It contents look like:

SELECT * FROM trips LIMIT 5;
 trip_id | daily_user_id |  session_ids  | seconds_start | lat_start  | lon_start  | seconds_end |  lat_end   |  lon_end   |     distance     
--------- --------------- --------------- --------------- ------------ ------------ ----- -------- ------------ ------------ ------------------
  540797 |          2169 | {43350}       |    1461056108 | 41.1250659 | -8.5993936 |  1461056424 | 41.1221733 | -8.6004883 | 412.658565594423
  546128 |          3096 | {84659,84663} |    1461847953 | 41.1787939 | -8.6078294 |  1461849730 | 41.1840573 | -8.6033242 | 3469.92906971906
  536069 |          1080 | {9837}        |    1460293763 | 41.1836186 | -8.6001802 |  1460294099 | 41.1836725 | -8.6001787 | 47.7817179218928
  537711 |          1373 | {17641,17689} |    1460590761 | 41.1477454 |  -8.611109 |  1460593908 | 41.1477451 | -8.6111093 | 1081.61337507529
  542407 |          2254 | {53112}       |    1461173383 | 40.9853811 | -8.5205261 |  1461173677 | 40.9873266 | -8.5003848 | 2224.13368208515

As we can see in the session_ids column, some records have 1 value, some multiple.

How I get a summary statistics of rows with 1 session_ids value, with 2, etc..?

CodePudding user response：

We can use cardinality to count the number of elements in session_ids and group by with the results.

select   cardinality(session_ids) as number_of_session_id_values
        ,count(*)
from     t
group by cardinality(session_ids)

Fiddle