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, ' - ') — user330315
– user330315, Commented Apr 27, 2016 at 8:15

justi · Accepted Answer · 2021-07-02 16:35:46Z

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 2, 2021 at 16:35

justi

4,1262 gold badges22 silver badges24 bronze badges

answered Apr 27, 2016 at 8:19

user330315

Sign up to request clarification or add additional context in comments.

4 Comments

lokmancetin Over a year ago

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.

user330315 Over a year ago

@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

lokmancetin Over a year ago

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.

user330315 Over a year ago

You can use any valid SQL expression in the where part of the filter

lokmancetin · Accepted Answer · 2016-04-27 11:29:27Z

0

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         |
+==========+========+===================+

edited Apr 27, 2016 at 11:29

lokmancetin

251 silver badge5 bronze badges

answered Apr 27, 2016 at 10:59

Barış Serkan AKIN

5895 silver badges14 bronze badges

Collectives™ on Stack Overflow

string aggregate group and count on a value

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related