lokmancetin lokmancetin - 7 months ago 14
SQL Question

string aggregate group and count on a value

I have table like this.

| table |
| class_id| name | gender |
+---------+---------+----------+
| 1 | Jane | F |
| 1 | John | M |
| 1 | Tom | M |
| 1 | Bob | M |
| 2 | Jack | M |
| 2 | Kate | F |


I have a query like this.

select id, array_to_string(array_agg(name), ' - '::text) as name_list from table
group by class_id


My result is

| 1 | Jane-John-Tom-Bob |


But i'd like to count my gender count also i mean in the first group (cass 1) i need a column like 1 F + 3 M

My request is something like this and i'd like to use it in 1 group by.

| 1 | Jane-John-Tom-Bob |1F + 3M

Answer

You can do that with a filtered aggregate:

select id, 
       string_agg(name), ' - ') as name_list, 
       concat( 
            count(*) filter (where gender = 'F'), 
            'F + ', 
            count(*) filter (where gender = 'M'), 
            'M') as gender_count
from table
group by class_id;

If you are on an older Postgres version, you need to replace

count(*) filter (where gender = 'F')

with

count(case when gender = 'F' then 1 end) 

(and the same for 'M')