1

I am using Microsoft Access to query a table named "AllData" which contains a column "gender".

The gender column has data that is a number type, the number in this column is either 1 (male), 2 (female), 3 (other), or null.

I want to count how many males there are. I tried using the answers to this previous question Is it possible to specify a condition in Count(). This is my best attempt at using the information from the top answer:

SELECT

COUNT(CASE gender WHEN 1 THEN 1 ELSE NULL END)

FROM AllData;

But when I run this query I get an error message "Syntax error (missing operator) in query expression 'COUNT(CASE gender WHEN 1 THEN 1 ELSE NULL END)'. I am very new to SQL and so I cannot figure out which part of that line might be leading to the problem.

Hugh
  • 413
  • 1
  • 5
  • 12

1 Answers1

2

The following will work in any database:

SELECT COUNT(*)
FROM AllData
WHERE gender = 1;

If you want all genders:

SELECT gender, COUNT(*)
FROM AllData
GROUP BY gender;

MS Access does not support the CASE expression.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786