Multiple count columns in one query

[gojets1721]

I have a query. There's a number field in the query where any record could have a value in it from 1 to 100.

I am trying to group the query by 'employeename' and then have two count columns for each employeename. A count column where the number field is below 50 and another count column where the number field is over 50.

I can only figure out how to have one count column (which results in me having to make two seperate queries). I was hoping it could be done in a single query. Here's my SQL currently if helpful:

SELECT EmployeeName, Count(Number) AS [CountOfNumber]
FROM qryEmployeeTotals
WHERE (((Number)>50))
GROUP BY EmployeeName;

 

[plog]

You move the criteria from the WHERE to an IIF and then SUM:

SELECT EmployeName, SUM(Iif(Number>50, 1, 0)) AS Over50
FROM source
GROUP BY EmployeeName

Just add the under 50 as a new field similar to the over 50. And then wonder why your counts are off (hint--anything exactly 50 won't appear in the query).

 

[MarkK]

You can also just sum the boolean expression, but then negate it, since True = -1, like...

SELECT EmployeName, -SUM(Number > 50) AS Over50
FROM source
GROUP BY EmployeeName