Apply the COUNT function to a subgroup of groups

Question

I made this strange example, trying to illustrate what I want to do (this is stupid, but carrying with me):

Consider the following table:

EMPLOYEES

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

I need to come up with SQL, which displays for each employee, the number of married, certified and religious employees in the following salary categories:

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

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

So far, I just came 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:

This is almost what I need, but I also need to divide these counters further into groups depending on the salary range.

I suppose some kind of analytic function that divides groups into buckets based on some SQL expression will help, but I can't figure out which one. I tried with NTILE , but it expects a positive constant as a parameter, not an SQL expression (e.g. SALARY BETWEEN X and Y ).

Answer 1

No, there is no need for analytic functions; in any case, they are difficult to obtain in the same query as the aggregate function.

You are again looking for a case , you just need to put it in the GROUP BY group.

 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 changed your count(case when...) to sum() . This is because you are using a boolean value of 1/0, so this will work the same, but it is much cleaner.

For the same reason, I ignored your between in your payroll; there is no special need for it, as if the salary was more than 2000, the first CASE has already been completed.