CODEWITHSUNDEEP
sqlpostgresql
Luffydude
Luffydude

Reputation: 772

How to count numbers in an aggregation in sql?

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

Upvotes: 1

Views: 1199

Answers (2)

klin
klin

Reputation: 121534

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)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 1

Related Questions