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
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