# Apply COUNT function on a subgroup of groups

I made up this weird example trying to illustrate what I want to do (it's kind of stupid, but bear with me):

Consider the following table:

EMPLOYEES

married, certified and religious are just boolean fields (in case of Oracle, they are of type NUMBER(1,0)).

I need to come up with SQL that displays for each hire_year, count of married, certified and religious employees within the following salary categories:

• A SALARY > 2000
• B SALARY BETWEEN 1000 AND 2000
• C SALARY < 1000

Based on the above dataset, here is what I expect to get:

So far, I've only come up with the following SQL:

```SELECT
COUNT(CASE WHEN married = 1 THEN 1 END) as MARRIED,
COUNT(CASE WHEN certified = 1 THEN 1 END) as certified,
COUNT(CASE WHEN religious = 1 THEN 1 END) as religious,
hire_year
FROM employees
GROUP BY hire_year;
```

The result of executing this SQL is:

Which is almost what I need, but I also need to divide these counters further down into the groups based on a salary range.

I guess that some analytic function, that divides groups into the buckets based on some SQL expression would help, but I can't figure out which one. I tried with NTILE, but it expects a positive constant as a parameter, rather than an SQL expression (such as SALARY BETWEEN X and Y).

Nope, no need for analytic functions; they're difficult to have in the same query as an aggregate function anyway.

You're looking for the case statement again, you just have to put it in the GROUP BY.

```select hire_year
, sum(married) as married
, sum(certified) as certified
, sum(religious) as religious
, case when salary > 2000 then 'A'
when salary >= 1000 then 'B'
else 'C' end as salary_class
from employees
group by hire_year
, case when salary > 2000 then 'A'
when salary >= 1000 then 'B'
else 'C' end
```

Note that I've changed your count(case when...) to sum(). This is because you're using a boolean 1/0 so this'll work in the same manner but it's a lot cleaner.

For the same reason I've ignored your between in your salary calculation; there's no particular need for it as if the salary is greater than 2000 the first CASE has already been fulfilled.