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
..........???

select count(decode(sex 'M' 1)) Males count(decode(sex 'F' 1)) Females from emp where city 'NGP';

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.

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

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

select sum(male) male sum(female) female

select count(sex) Male null Female

from test_table

where sex 'M'

select null Male count('SEX') Female

from test_table

where sex 'F'

group by sex)