PostgreSQL Aggregate Functions

Sometimes, when we use SQL aggregate functions, we hope to collect all primary keys in each column group.

Take below for example:

SELECT date_trunc('day', create_time) AS date,
       count(id)
FROM s_table
GROUP BY date
ORDER BY date;

In this sql code, we can just get columns grouped by or aggregate results; we can not get id columns (or others) in each row.

But how can we get that ? We can use string_agg:

SELECT date_trunc('day', create_time) AS date,
       count(id),
       string_agg(cast(id as text), ',')
FROM s_table
GROUP BY date
ORDER BY date;

Maybe you have noticed a comma , as sting_agg’s second parameter, It is just a delimiter, you can use any one you would like.

So you maybe get some records as below:

2015-01-17 00:00:00+08 | 5 | 1461491,1461508,1461482,1461483,1461509

It is very Excited !