0

I have the following query: 

    SELECT count(Committees)
    FROM [Annual Questionnaire]
    WHERE Committees=TRUE;

However, I'd like to include other columns in the result e.g. Insurance, Equalities and counted where the value is True e.g. Insurance=True. Not all the columns in the table have to be counted.

I think a pseudo query would be: 

SELECT count(Committees),
       count(Insurance)
FROM [Annual Questionnaire]
WHERE Committees=TRUE
  AND Insurance=TRUE;

^ This doesn't work because it selects rows where only Committees and Insurance is True

Basically, how do I count the specified columns where the value is True?

Justin
  • 9,634
  • 6
  • 35
  • 47
Sheldon
  • 9,639
  • 20
  • 59
  • 96

2 Answers2

3

you can do something like

SELECT 
    SUM(IIF(Committees=True, 1, 0))
    , SUM(IIF(Insurance=True, 1, 0))
FROM [Annual Questionnaire]
T I
  • 9,785
  • 4
  • 29
  • 51
  • Thanks. I'm a little rusty on sql. For bonus points, what would be the case syntax for this? http://stackoverflow.com/questions/12789396/how-to-get-multiple-counts-with-one-sql-query – Sheldon Apr 09 '13 at 10:23
  • 1
    access does not have case syntax – T I Apr 09 '13 at 10:45
1

If you specify both in your WHERE clause, it will only return rows that have both columns true. Remember that the order of execution is the Where clause been executed before the Select, so it will filter down the data based on what you have and then select whatever you told it to select.

This is the order of execution:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

There are similar threads already: How to get multiple counts with one SQL query?

Community
  • 1
  • 1