A table has following layout ; CITY, NAME , SEXHow do you write a query to get the count of Male, count of female in a given city XXX.Query result should be in a single row with count of male , count of female as columns?

  • Interview Candidate
  • 44
  • 9287

Showing Answers 1 - 44 of 44 Answers

prafull.vn

  • Mar 20th, 2006
 

select emp1.city,emp1.male male,emp2.female female from
   (select city,count(sex) as male from emp where sex = 'M' group by city) emp1,
  (select city,count(sex) as female from emp where sex = 'F' group by city) emp2
where emp1.city = emp2.city
and emp1.city='NGP'

THIS QUERY WILL NOT WORK IF THERE IS NO MALE RECORD OR FEMALE RECORD .. ie there should be at least one male and one female in one city ..

can anybody has other option ... like if in City NGP there are males only then ... Output should be .. NGP,10,0 ... males =10 and NO females..or vice versa NGP,0,10
..........???

  Was this answer useful?  Yes

padma

  • Mar 23rd, 2006
 

select count(decode(job,'MANAGER',1)) mgr,
count(decode(job,'SALESMAN',1)) salman from emp where to_char(hiredate, 'yy') = '81'

  Was this answer useful?  Yes

Nabil Essa

  • Mar 29th, 2006
 

select count(*) ,'Male' from tablewhere city='xxx'and sex='M'union select count(*), 'Femal' from tablewnere city='xxx'and sex='F'

  Was this answer useful?  Yes

Ashutosh

  • Mar 31st, 2006
 

select (Select count(*) From tab where City = 'XXX' and Sex = 'Male') as Male_Cnt,
       (Select count(*) From tab where City = 'XXX' and Sex = 'Male') as Female_Cnt
From Dual

This will wrk too.

  Was this answer useful?  Yes

Shaguna

  • Apr 6th, 2006
 

select City, sum(decode(sex, 'M', 1, 0)) Male, sum(decode(sex, 'F', 1, 0)) Female from <table_name> group by city

  Was this answer useful?  Yes

tarun1282

  • Apr 27th, 2006
 

select count(male.name) as malecount,count(female.name) as femalecount from (select name from table where sex='m') male,(select name from table where sex='f') female where city='XXX';

  Was this answer useful?  Yes

Pushpendu

  • Jun 14th, 2006
 

I think prafull's approach is fine.Another simple way of doing it is

SELECT CITY, COUNT(MALE), COUNT(FEMALE)
FROM
( SELECT  CITY,
  case  WHEN SEX='M' THEN  'M' ELSE NULL  END  MALE,
   case WHEN SEX='F'  THEN  'F'  ELSE NULL   END  FEMALE
   FROM TAB_PT
)
GROUP BY CITY

Adt

  • Jul 13th, 2006
 

select sum(male) male,sum(female) female

from(

select count(sex) Male, null Female

from test_table

where sex= 'M'

union

select null Male , count('SEX') Female

from test_table

where sex= 'F'

group by sex)

  Was this answer useful?  Yes

Shiv Mangal Rahi

  • Jul 21st, 2006
 

declare
v_totalmale binary_integer;
v_totalfemale binary_integer;
begin
select count(sex) into v_totalmale from test where city='Delhi' and sex='Male';
select count(sex) into v_totalfemale from test where city='Dlehi' and sex='Female';
dbms_output.put_line('    TotalMale' || '    ' || 'TotalFemale');
dbms_output.put_line('      ' || v_totalmale || '             '|| v_totalfemale);
end;

  Was this answer useful?  Yes

Som

  • Oct 5th, 2006
 

simplified one, hope this one soothes...(uses a correlated subquery)

select distinct CITY,(select count(SEX) from tab t1 where t1.CITY=t2.CITY and t1.SEX = 'M') as MALE,(select count(SEX) from tab t1 where t1.CITY=t2.CITY and t1.SEX = 'F') as FEMALE

from tab t2

Group by CITY,SEX

  Was this answer useful?  Yes

Ravi

  • Oct 25th, 2007
 

Hi Use the following query

select city,(count(case when gender = 'Male' then 1 else 0 end)) 'male',
(count(case when gender = 'Female' then 1 else 0 end)) 'female'
from table
where city = 'give the city'


you will get the answer u want

  Was this answer useful?  Yes

vishakha_jain81

  • Aug 12th, 2008
 

select city,count(decode(sex,'M',1)) Males,count(decode(sex,'F',1)) Females from sex where city in (select city from sex) group by city;

or

 select city,count(decode(sex,'M',1)) Males,count(decode(sex,'F',1)) Females,name from sex
where city in (select city from sex)
group by city,sex,name

  Was this answer useful?  Yes

Arvind209

  • Sep 29th, 2008
 

CREATE TABLE A

(

CITY VARCHAR2(10 BYTE),

NAME VARCHAR2(20 BYTE),

SEX CHAR(1 BYTE)

);

Table's Record as:-
City Name      Sex
A     |arvind    |M
A     |Pawan|   M
A     |Jitu|        M
A     |Hema|    F
A     |Sita        |F

select

sum(a) ||','||sum(b) "M,F" from

(select decode(Sex,'M',count(Sex)) a, decode(Sex,'F',count(Sex)) b

from A where City='A' group by Sex)




 

  Was this answer useful?  Yes

Arvind209

  • Sep 29th, 2008
 

select sum(a) ||','||sum(b) "M,F" from

(select decode(Sex,'M',count(Sex)) a, decode(Sex,'F',count(Sex)) b

from ABC where City='A' group by Sex)

  Was this answer useful?  Yes

ashishkumar2403

  • Jan 2nd, 2010
 

SELECT city,DECODE(sex,'M',COUNT(sex),null)male, DECODE(sex,'F',count(sex),null)female FROM test GROUP BY city,sex;

This will give the name of city along with corresponding number of male and female on that city.

  Was this answer useful?  Yes

rajayoghi

  • Jan 6th, 2010
 

select count(case when sex='male' then 1 end) "male", count(case when sex='female' then 1 end)"male", city from cnt where city = 'bangalore';

  Was this answer useful?  Yes

Karthik K Shetty

  • Jun 20th, 2016
 

Code
  1. SELECT (SELECT COUNT(NAME) FROM TABLE_NAME WHERE UPPER(SEX) LIKE M%) AS COUNT_OF_MALE,

    2. 

  2.            (SELECT COUNT(NAME) FROM TABLE_NAME WHERE UPPER(SEX) LIKE F%) AS COUNT_OF_FEMALE

    3. 

  3. FROM DUAL;

  Was this answer useful?  Yes

Bibhudatta Pand

  • Jun 18th, 2019
 

Suppose below is the city table contains
NAME SEX
Bangalore FEMALE
Bangalore MALE
Bangalore MALE
Bangalore FEMALE
Bangalore MALE
Bangalore MALE
Hyderabad MALE
Hyderabad MALE
Hyderabad MALE
Hyderabad FEMALE
Hyderabad FEMALE
Hyderabad FEMALE
SQL> SELECT COUNT(*),DECODE(SEX,MALE,MALE,FEMALE,FEMALE,UN) cnt_sex from city where Name=Hyderabad group by sex;

Code
  1. SQL CODE

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions