Since my times using Crystal Reports (around 2005) I've been used to "a group by flattens all your columns into one value". It was something inescapable (unless big hacks)
But lately, with complex types in postgres, and smarter aggregation functions, I've grown to create nested structures in group bys when I want to retain information about individual records inside the group.
It probably violates a few normalization rules, but who cares?
So here's it:
select country, count(*), jsonb_pretty(jsonb_agg((id, status, deleted_at) order by deleted_at desc)) as statuses from customer group by country having count(*)>1 order by count desc;I think it's super cool to be able to generate a result with all the nested information of the integrants of each group, and even more, being able to order by, inside the groups!