Search code examples
sqlpostgresqlgroup-bycountarray-agg

How to count on a group by array_agg in PostgreSQL

Here is my SQL:

SELECT t.uid, array_agg(t.place) FROM tour_tracking t WHERE (orderon::time BETWEEN '18:00:00' AND '20:00:00') GROUP BY t.uid;

Origin result:

uid place
a01 {hk, hk, jp}
a02 {jp, jp, jp, jp, uk}

Now I want to count on each DISTINCT place for every group-by-ed uid. Hopeful result:

uid place
a01 something like this: {hk,2, jp,1}
a02 {jp:4, uk:1}

I try to combine some count() sql query but won't work.., how to do the right query?

PostgreSQL version: 10.3

Solution

  • Aggregate twice. Once to get the places and their counts, then again to make the lists...

    SELECT
  t.uid,
  array_agg(array[t.place, t.row_count])
FROM
(
  SELECT
    uid,
    place,
    COUNT(*)   AS row_count
  FROM
    tour_tracking
  WHERE
    orderon::time BETWEEN '18:00:00' AND '20:00:00'
  GROUP BY
    uid,
    place
)
  t
GROUP BY
  t.uid