4

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

stackoverYC
  • 490
  • 2
  • 4
  • 13

1 Answers1

9

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
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • `array_agg` doesn't take an array as input. – 404 May 17 '18 at 16:19
  • @eurotrash : Then the docs lie... https://www.postgresql.org/docs/9.5/static/functions-aggregate.html – MatBailie May 17 '18 at 16:21
  • 1
    Hmm, fair enough, seems like a change that came after the version I use (9.3), and something I've often wished for. – 404 May 17 '18 at 16:24
  • 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])` – nachocab Mar 01 '20 at 09:46