title: Apply COUNT function on a subgroup of groups
body: <p>I made up this weird example trying to illustrate what I want to do (it's kind of stupid, but bear with me):  </p>
  
  <p>Consider the following table:  </p>
  
  <p><strong>EMPLOYEES</strong><br>
  <img src="https://i.stack.imgur.com/4HE9s.png" alt="Employees table data">  </p>
  
  <p><em>married</em>, <em>certified</em> and <em>religious</em> are just boolean fields (in case of Oracle, they are of type NUMBER(1,0)).  </p>
  
  <p>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:  </p>
  
  <ul>
  <li><strong>A</strong> <code>SALARY &gt; 2000</code></li>
  <li><strong>B</strong> <code>SALARY BETWEEN 1000 AND 2000</code></li>
  <li><strong>C</strong> <code>SALARY &lt; 1000</code>  </li>
  </ul>
  
  <p>Based on the above dataset, here is what I expect to get:</p>
  
  <p><img src="https://i.stack.imgur.com/pwSOj.png" alt="Expected result">  </p>
  
  <p>So far, I've only come up with the following SQL:  </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The result of executing this SQL is:  </p>
  
  <p><img src="https://i.stack.imgur.com/aXC7o.png" alt="Actual result"></p>
  
  <p>Which is almost what I need, but I also need to divide these counters further down into the groups based on a salary range.  </p>
  
  <p>I guess that some <em>analytic</em> 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 <strong>NTILE</strong>, but it expects a positive constant as a parameter, rather than an SQL expression (such as <code>SALARY BETWEEN X and Y</code>).</p>