Calculate column percentages using SQL in MS Access

Question

I have a table called 'DEMO'. It has a column called 'SEX' with records, i.e. 'Male', 'Female' or blank.

I want to find the percentage of 'Males' by:

(Counting 'Males' / (Total records - Blank records)) * 100

all in a single query.

I'm using MS Access 2010 with a .accdb file.

Answer 1

Create a Crosstab query to separate the values.
Create a new query with the formula, turn on totals so you only get one line in the return. Clunky, but effective.

Answer 2

With Access you can't use CASE WHEǸ syntax, thoughIIF is similar :

SELECT
  SUM(IIF(SEX = 'Male', 1.0, 0.0))
  / COUNT(*) AS RATIO
FROM
  DEMO
WHERE
  SEX <> ''

Answer 3

I tried the answer that psadac gave with a slight change and it seemed to work fine for me. I am using Access 2010.

SELECT Sum(IIf([SEX]= "Male",1,0))/Count(*) AS Ratio
FROM DEMO
HAVING (((Sum(IIf([SEX]= "Male",1,0))/Count(*)) Is Not Null));