| Author: Priya Rohan Marc 04 Aug 2005 | Member Level: Silver | Rating:  Points: 2 |
select distinct a.city ,
(Select count(*) from citydetails where sex = 'M' and city in (select a.city)) as Male,
(Select count(*) from citydetails where sex = 'F' and city in (select a.city)) as Female
from citydetails a
|
| Author: santhi priya peddi 04 Aug 2005 | Member Level: Silver | Rating: Points: 2 |
hi..
Select x.City , Sum(X.Male) as Male , Sum(X.Female) as Female from
(select City,count(Sex) as Male,0 as Female from E1 Where Sex='M' group by City
Union
select City,0 as Male ,count(Sex) as Female from E1 Where Sex='F' group by City)
x Group by X.City
Try this n hope u get answer for ur ?
keep smiling,
santhi.
|
| Author: Ashish Singh Khichi 06 Aug 2005 | Member Level: Bronze | Rating: Points: 2 |
SELECT a.city, a.Male, b.feMale
FROM (SELECT city, COUNT(*) AS Male
FROM ex
WHERE sex = 'M'
GROUP BY city) a LEFT OUTER JOIN
(SELECT city, COUNT(*) AS feMale
FROM ex
WHERE sex = 'F'
GROUP BY city) b ON a.city = b.city
|
| Author: Ashish Singh Khichi 06 Aug 2005 | Member Level: Bronze | Rating: Points: 2 |
SELECT a.city, a.Male, b.feMale
FROM (SELECT city, COUNT(*) AS Male
FROM ex
WHERE sex = 'M'
GROUP BY city) a LEFT OUTER JOIN
(SELECT city, COUNT(*) AS feMale
FROM ex
WHERE sex = 'F'
GROUP BY city) b ON a.city = b.city
|
| Author: AbiBaby (Abishek Bellamkonda) 10 Aug 2005 | Member Level: Gold | Rating: Points: 2 |
The same query can be written as below in MS Access.
SELECT CityName, SUM(IsMale) As NoOfMales, SUM(IsFemale) As NoOfFemales
FROM (
SELECT CityName, IIF(Sex='M', 1,0)As IsMale, IIF(Sex='F', 1,0) as IsFemale
FROM Citytable
) GROUP BY CityName;
|