Posgresql - find gender count

Question

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

It may not your answer but you can search "crosstab" function on Postgres. This function works like your question. — G.Guvenal, Mar 04 '23 at 10:57
In your expected result format; The Male count should be 3 and Female Count 2 based on the input data (table) — Tushar, Mar 04 '23 at 11:14

SelVazi · Answer 1 · 2023-03-04T11:41:08.820

1

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

edited Mar 04 '23 at 11:41

answered Mar 04 '23 at 11:33

SelVazi

10,028
2
13
29

1

By the time I was resolving `ERROR: invalid input syntax for integer:`; You completed the answer !! +1 – Tushar Mar 04 '23 at 11:41
1

Yes, datatypes in union rows must be the same – SelVazi Mar 04 '23 at 11:44
1

Yup ! Offcourse :) – Tushar Mar 04 '23 at 11:50
Have any other option .. without using union and union all – Learn Hadoop Mar 04 '23 at 12:09

Tushar · Answer 2 · 2023-03-04T11:47:40.647

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.

edited Mar 04 '23 at 11:47

answered Mar 04 '23 at 11:06

Tushar

3,527
9
27
49

1

my expected output format is different. Please have a look. Gender value also one of the row – Learn Hadoop Mar 04 '23 at 11:15
Ok, I will add it to the query – Tushar Mar 04 '23 at 11:15
1

@Tushar You're emulating a SQL:2003 [aggregate filter](https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES) available in [PostgreSQL since 9.4](https://www.postgresql.org/docs/release/9.4.0/#AEN124521): `count(*) filter (where gender = 'Male')`. – Zegarek Mar 04 '23 at 11:24
Yes @Zegarek; As SO needs a output in different format. SO has already used `count(*)` – Tushar Mar 04 '23 at 11:29
@Zegarek; Yup; The other user has completed answer using `count(*)` :) – Tushar Mar 04 '23 at 11:45
1

@Tushar I wasn't suggesting that's *the* way to solve this but just pointing out that you're emulating something that's been long available - just letting you know you no longer need to. It's not about `count()`, it's about the `filter (where`. The `count()` could also have been used with `filter` emulation: `count(case when gender = 'Male' then 1 end)` - non-male will result in null and get discarded by the function, therefore counting only 'Male'. And vice versa, you could deploy `sum()` with a filter: `sum(1) filter (where gender = 'Male')`. – Zegarek Mar 04 '23 at 12:01
@Zegarek; Yup. You are right and `a kind soul guiding people to write queries in lot of ways` !! I appreciate that – Tushar Mar 04 '23 at 12:02

score 0 · Answer 3 · answered Mar 04 '23 at 12:04

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

score 0 · Answer 4 · answered Mar 04 '23 at 13:23

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

Posgresql - find gender count

4 Answers4