So I want to aggregate groups of points that share the same location and then count the number of each class that belongs to the point.
My aggregation query is as follows:
create table mytable2 as
select count(*) as rows, location, string_agg(distinct class, ', ' order by class)
from mytable
group by location
The outcome of this gives me a row of for example
(16, 'Wakanda', 'warrior priest tank')
How do I aggregate it to show instead
(16, 'Wakanda', '10 warrior 5 priest 1 tank')
Example data:
create table mytable(location text, class text);
insert into mytable values
('Wakanda', 'warrior'),
('Wakanda', 'warrior'),
('Wakanda', 'priest'),
('Wakanda', 'tank'),
('Wakanda', 'tank'),
('Wakanda', 'warrior');
Use grouping sets. You can easily get a nice tabular output:
select location, class, count(*)
from mytable
group by grouping sets ((location), (location, class));
location | class | count
----------+---------+-------
Wakanda | priest | 1
Wakanda | tank | 2
Wakanda | warrior | 3
Wakanda | | 6
(4 rows)
or a single row for a location, e.g.:
select
max(count) as units,
location,
string_agg(class || ': ' || count, ', ') as counts
from (
select location, class, count(*)
from mytable
group by grouping sets ((location), (location, class))
) s
group by location;
units | location | counts
-------+----------+--------------------------------
6 | Wakanda | priest: 1, tank: 2, warrior: 3
(1 row)
If I understand correctly, you want two levels of aggregation:
select lc.location,
string_agg(class || '(' || cnt || ')', ', ' order by cnt desc)
from (select location, class, count(*) as cnt
from mytable
group by location, class
) lc
group by lc.location;
I put the string in what I consider a more reasonable format. It would look like: 'warrior (10), priest (5), tank (1)'. It is ordered by the frequency. You can (of course) adjust the syntax to get some other format if you like.
