Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table user_request_1(
  name varchar(20),
  gender varchar(20)
);
  
  
insert into user_request_1 values 
('AA','Male'),
('BB','Male'),
('CC','Male'),
('DD','Female'),
('EE','Female');

CREATE TABLE



INSERT 0 5


select 
  MIN(CASE WHEN r=1 THEN gender END) as "gender-1",
  MIN(CASE WHEN r=2 THEN gender END) as "gender-2",
  SUM(CASE WHEN r=1 THEN 1 ELSE 0 END) as "count-1",
  SUM(CASE WHEN r=2 THEN 1 ELSE 0 END) as "count-2"
from 
(  select 
     gender, 
     dense_rank() over (order by gender) r
   from user_request_1
) x
where r<=(select  count(distinct gender) from user_request_1);


gender-1
gender-2
count-1
count-2


Female
Male
2
3



SELECT 1


WITH ABC as 
(  select 
     gender, 
     dense_rank() over (order by gender) r
   from user_request_1
) 
select 
  MIN(CASE WHEN r=1 THEN gender END) as "gender-1",
  MIN(CASE WHEN r=2 THEN gender END) as "gender-2"
from ABC
union all
select 
  SUM(CASE WHEN r=1 THEN 1 ELSE 0 END)::varchar as "count-1",
  SUM(CASE WHEN r=2 THEN 1 ELSE 0 END)::varchar as "count-2"
from ABC


gender-1
gender-2


Female
Male

2
3



SELECT 2