string aggregate group and count on a value

Question

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

`array_to_string(array_agg(name), ' - '::text)` can be simplified to `string_agg(name, ' - ')` — , Apr 27 '16 at 08:15

score 4 · Accepted Answer · edited Jul 02 '21 at 16:35

4

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

edited Jul 02 '21 at 16:35

justi

3,887
2
18
24

answered Apr 27 '16 at 08:19

Thanks for the help. That's great. For further use, if i don't know the values but i know a column that i can use for group by how can i use it in the filter? Maybe i can use more than 10 filter value. – lokmancetin Apr 27 '16 at 08:45
@lokmancetin: sorry I don't understand you. The column you group by has nothing to do with the column you apply the filtered aggregate on – Apr 27 '16 at 09:01
In your answer, the filter values are common like F and M. Simple. But what if the filter values is more than 50 i.e and this 50 values is also in the column in this current table. I can get his values by group by and i'd like to use these group byed values in the filter aggregate. – lokmancetin Apr 27 '16 at 10:12
You can use any valid SQL expression in the `where` part of the filter – Apr 27 '16 at 10:49

score 0 · Answer 2 · edited Apr 27 '16 at 11:29

There is also another solution without using Filter aggregate

select tt.class_id, string_agg ( t,  ','::text) as gender, string_agg(distinct y,','::text) as name

from 

(

select class_id, count(gender)::text|| string_agg( distinct  gender, ','   ) as  t
       from string_test
group by class_id , gender

) tt , 

(
select class_id, string_agg( distinct name::text, ','::text ) as y
    from string_test
group by class_id
) yy
where tt.class_id=yy.class_id

group by tt.class_id

Result;

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

string aggregate group and count on a value

2 Answers2