CODEWITHSUNDEEP
sqlms-access
Alice
Alice

Reputation: 3

Only counting data that fits certain criteria in SQL on Microsoft Access

I have 1 table in microsoft access with student information (including gender and grade). I'm trying to use the SQL view to get a count of how many male and females are in each grade. Right now, this is what I have. 

SELECT 
StudDetails.CurrClass as 'class', 
Count(StudeDetails.sex) WHERE (StudDetails.sex="M" AND StudDetails.CurrClass='class') AS 'malecount', Count (StudeDetails.sex) WHERE (StudDetails.sex="F"AND StudDetails.CurrClass='class') AS 'femalecount'
FROM StudDetails
GROUP BY StudDetails.CurrClass;

I know this is super bad but I'm new to SQL. I've tried googling but I don't understand the answers or can't apply them.

Upvotes: 0

Views: 118

Answers (3)

Erik A
Erik A

Reputation: 32642

You can't use WHERE the way you're using it. You're looking for IIF to do comparisons.

Since Count is rather inflexible, using Sum and assigning ones and zeroes on a comparison is easier.

SELECT 
StudDetails.CurrClass as 'class', 
Sum(IIF(StudDetails.sex="M", 1, 0)) AS malecount, 
Sum(IIF(StudDetails.sex="M", 0, 1))  AS femalecount
FROM StudDetails
GROUP BY StudDetails.CurrClass;

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

Given two fields in the table - Class and Gender

The table on the left will give the results shown on the right with this SQL: 

SELECT      Class
            , Sex
            , COUNT(Sex) AS CountOfGender
FROM        StudDetails
GROUP BY    Class, Sex

The only downside here is that it won't show 0 if there's none of a certain gender in a class - it just won't show that gender.

+-------+--------+  +-------+--------+---------------+
| Class | Sex    |  | Class | Sex    | CountOfSex    |
+-------+--------+  +-------+--------+---------------+
|   A   |    M   |  |   A   |    F   |       2       |
+-------+--------+  +-------+--------+---------------+
|   B   |    M   |  |   A   |    M   |       3       |
+-------+--------+  +-------+--------+---------------+
|   C   |    F   |  |   B   |    M   |       1       |
+-------+--------+  +-------+--------+---------------+
|   A   |    F   |  |   C   |    F   |       1       |
+-------+--------+  +-------+--------+---------------+
|   A   |    M   |  |   D   |    M   |       2       |
+-------+--------+  +-------+--------+---------------+
|   A   |    M   |  
+-------+--------+  
|   D   |    M   |  
+-------+--------+ 
|   D   |    M   |  
+-------+--------+ 
|   A   |    F   | 
+-------+--------+

Edit: To get Male & Female in separate fields and include 0 you could then just cross-tab the query above: 

TRANSFORM NZ(Sum(CountOfSex),0) AS CountOfGender
SELECT Class
FROM (
        SELECT      Class
                    , Sex
                    , COUNT(Sex) AS CountOfSex
        FROM        StudDetails
        GROUP BY    Class, Sex
      )
GROUP BY Class
PIVOT Sex  

+-------+---+---+
| Class | F | M |
+-------+---+---+
|   A   | 2 | 3 |
+-------+---+---+
|   B   | 0 | 1 |
+-------+---+---+
|   C   | 1 | 0 |
+-------+---+---+
|   D   | 0 | 2 |
+-------+---+---+

Edit again: The good thing about this method above conditionally counting Male & Female (IIF sex='M'.. etc) is that if someone decides they don't want to identify as Male or Female it will happily list any other gender you can think of. 

+-------+---+---+---+
| Class | F | M | Z |
+-------+---+---+---+
|   A   | 2 | 3 | 0 |
+-------+---+---+---+
|   B   | 0 | 1 | 0 |
+-------+---+---+---+
|   C   | 1 | 0 | 0 |
+-------+---+---+---+
|   D   | 0 | 2 | 1 |
+-------+---+---+---+

Upvotes: 1

tonypdmtr
tonypdmtr

Reputation: 3225

I'm not sure about ms-access capabilities so these possibilities may not all work for you but at least one should.

-- Some sample data

create table StudDetails(sex char(1),currclass varchar(10));

insert into StudDetails values
('M','ClassA'), ('M','ClassA'), ('F','ClassA'),
('F','ClassB'), ('F','ClassB'), ('F','ClassB'), ('M','ClassB'), ('F','ClassB'),
('F','ClassC'), ('F','ClassC');

-- One method

select CurrClass as "class",
       sum(case when sex='M' then 1 else 0 end) as "malecount",
       sum(case when sex='F' then 1 else 0 end) as "femalecount"
  from StudDetails
 group by CurrClass;

-- Another method

with xxx(CurrClass,malecount,femalecount) as
  (
   select CurrClass,1,0 from StudDetails where sex = 'M'
   union all
   select CurrClass,0,1 from StudDetails where sex = 'F'
  )
  select CurrClass,sum(malecount) as malecount,sum(femalecount) as femalecount
    from xxx
    group by CurrClass;

-- Yet another method

select CurrClass,sum(malecount) as malecount,sum(femalecount) as femalecount
  from (
        select CurrClass,1 as malecount,0 as femalecount from StudDetails where sex = 'M'
        union all
        select CurrClass,0 as malecount,1 as femalecount from StudDetails where sex = 'F'
       ) xxx
  group by CurrClass;

Upvotes: 0

Related Questions