Grouped Multiple Count Query

[Matt]

I'm trying to run a query that displays a gender count for each job
classification.

ie
(Classification, Male, Female)
HEW1 10 15
HEW2 10 20
TAF1 20 10

with this query:
SELECT alldata.classification,
(SELECT COUNT(*) FROM alldata WHERE gender = "M") AS M ,
(SELECT COUNT(*) FROM alldata WHERE gender = "F") AS F
FROM alldata
group by classification

I can get it to list each classification, but the gender totals are a
count for the WHOLE report not for each individual classification
ie
HEW1 40 35
HEW2 40 35
TAF1 40 35

Is there anyway I can make it so the count query just refers to a
particular classification. I know I could put a where query in there
but then that limits me to just viewing one classification in the
query at a time.

This would be really great if someone could help me solve this because
there's several other reports that follow the same format.

 

[Michel Walsh]

Hi,


SELECT classification, SUM(iif(gender='m', 1, 0)) AS M, SUM(iif(gender='f',
1, 0)) As F
FROM alldata
GROUP BY classification


or do a crosstab.


Hoping it may help,
Vanderghast, Access MVP

 

[mbretag]

Thanks for your help! That example works great and seems to be a lot
quicker and simpler than what I used to have:

SELECT Ad.CLASSIFICATION, (SELECT COUNT(*)
FROM dataset
WHERE Classification = Ad.Classification AND
Gender = "F") AS Female, (SELECT
COUNT(*)
FROM dataset
WHERE Classification = Ad.Classification AND
Gender = "M") AS Male
FROM dataset AS Ad
GROUP BY Ad.CLASSIFICATION;

 

[MGFoster]

Thanks for your help! That example works great and seems to be a lot
quicker and simpler than what I used to have:

SELECT Ad.CLASSIFICATION, (SELECT COUNT(*)
FROM dataset
WHERE Classification = Ad.Classification AND
Gender = "F") AS Female, (SELECT
COUNT(*)
FROM dataset
WHERE Classification = Ad.Classification AND
Gender = "M") AS Male
FROM dataset AS Ad
GROUP BY Ad.CLASSIFICATION;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's an even faster one:

SELECT Classification, Count(IIf(Gender="F"),1)) As Female,
Count(IIf(Gender="M",1)) As Male
FROM dataset
GROUP BY Classification
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnBMTIechKqOuFEgEQL6vwCfa1teYrZS+yrSkCnqL+AwFTcy3DwAn0RO
lI7cW2nVomXVnXRc7UJCSCG+
=iH29
-----END PGP SIGNATURE-----