Question 89525 in PostgreSQL Question posted in
The official documentation can be found here.

Posgresql – find gender count – Postgresql

This my dataset and i want to find No. of gender

name|gender|
----+------+
AA  |Male  |
BB  |Male  |
CC  |Male  |
DD  |Female|
EE  |Female|

sql query :

    select 
    gender,count(*)
from 
    schoolofit.user_request_1
group by gender

result:

gender|count|
------+-----+
Female|    2|
Male  |    3|

But i am looking for below result format

gender-1 | gender-2
-------------------
Male     | Female
3        |  2

Note : looking for sql query with and without using Union

Tags:

4

Answers


  1. 0

    I understand that you want to convert row values of your current query result to columns

    This can be achieved using CASE STATEMENT :

    select 
    SUM(case when gender = 'Male' then 1 else 0 end) AS "gender-1",
    SUM(case when gender = 'Female' then 1 ELSE 0 end) AS "gender-2"
from 
    user_request_1;

    Update :
    Based on the input and clarification :

    select * from (select 'Male' AS "gender-1", 'Female' AS "gender-2"
UNION
select 
  CAST(SUM(case when gender = 'Male' then 1 else 0 end) as varchar) AS "gender-1",
  CAST(SUM(case when gender = 'Female' then 1 else 0 end) as varchar) AS "gender-2"
from user_request_1) as user_request
order by "gender-1" desc;

    Here is the SQLFiddle; where I have used data provided by you in post.

    Login or Signup to reply.
  2. 0

    You can do it using filter clause :

    CAST count to varchar to be able to union the gender count with the first row (male, female).

    select *
from (
   select 'Male' as "gender-1", 'Female' as "gender-2"
  Union
   select CAST(count(*) filter (where gender = 'Male') as varchar) as "gender-1",
   CAST(count(*) filter (where gender = 'Female') as varchar) as "gender-2"
   from user_request_1
) as s
order by "gender-1" desc

    Demo here

    Login or Signup to reply.
  3. 0

    When the values can be dynamic, and not just ‘Male’ or ‘Female’ you can do:

    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

    see: DBFIDDLE

    Login or Signup to reply.
  4. 0

    One option to avoid using a union, you can use an array then unnest it as the following:

    select unnest(mcnt) as gender_1,
       unnest(fcnt) as gender_2 
from
(
  select 
   array['Male', count(*) filter (where gender = 'Male')::text] as mcnt,
   array['Female', count(*) filter (where gender = 'Female')::text] as fcnt
  from table_name
) t

    Demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search