CODEWITHSUNDEEP
sqlms-access
Sheldon
Sheldon

Reputation: 10105

How do I count more than one column with a where clause? (SQL)

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?

Upvotes: 0

Views: 1222

Answers (2)

T I
T I

Reputation: 9943

you can do something like

SELECT 
    SUM(IIF(Committees=True, 1, 0))
    , SUM(IIF(Insurance=True, 1, 0))
FROM [Annual Questionnaire]

Upvotes: 3

user2203056
user2203056

Reputation:

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?

Upvotes: 1

Related Questions