How to count on a group by array_agg in PostgreSQL

Question

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

MatBailie · Accepted Answer · 2018-05-17 16:10:37Z

10

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

answered May 17, 2018 at 16:10

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

404 Over a year ago

array_agg doesn't take an array as input.

MatBailie Over a year ago

@eurotrash : Then the docs lie... postgresql.org/docs/9.5/static/functions-aggregate.html

404 Over a year ago

Hmm, fair enough, seems like a change that came after the version I use (9.3), and something I've often wished for.

nachocab Over a year ago

Note that I was getting

PG::DatatypeMismatch: ERROR:  ARRAY types text and bigint cannot be matched LINE 118:   array_agg(ARRAY[action_name, row_count])

, so I changed it to array_agg(array[t.place::varchar, t.row_count])

Collectives™ on Stack Overflow

How to count on a group by array_agg in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related