how to declare variable and get result set in postgresql?

Question

I have table named Students in PostgreSQL. In that table i have StudId, StudName and Gender columns. I want to get the number of male and female students in the table in a single result set in a function.

StudId   StudName    Gender
101      Peter       M
102      George      M
103      Mary        F
104      Rose        F

I already done this with SQL Server and MySQL. But I don't have any idea to do this with PostgreSQL.

I'm newer one to PostgreSQL, someone helps to finds the results. Thanks in advise.

Community · Accepted Answer · 2017-05-23 12:08:05Z

2

Use two sub-selects like this

select (select count(*) cnt_male from tbl where gender='M') 
      ,(select count(*) cnt_female from tbl where gender='F')

or

You can use case in count() function like this

select count(case when gender='M' then 1 end) cnt_male
      ,count(case when gender='F' then 1 end) cnt_female 
from tbl

Result:

cnt_male cnt_female 
-------- ---------- 
2        2

Edit:

as per op's update

create function fn_gender_cnt() returns table(tot_male bigint,tot_female bigint)
as
$$

  select count(case when gender='M' then 1 end) cnt_male
      ,count(case when gender='F' then 1 end) cnt_female 
from tbl

$$
language sql

usage:

select * from fn_gender_cnt();

result:

    tot_male tot_female 
    -------- ---------- 
    2        2

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Feb 2, 2016 at 4:23

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

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

3 Comments

DineshDB Over a year ago

Thank you Friend. I found the logic already, but I don't know how to get the two results in a Function. Sorry for the mistake, now I update the Question.

Vivek S. Over a year ago

@DineshDB I've updated my answer ...added a function to get the counts, is that what you were looking for??

DineshDB Over a year ago

Yes, thank you so much for your help. It works pretty good... Thanks a lot.

Collectives™ on Stack Overflow

how to declare variable and get result set in postgresql?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related