title: Count condition in CASE clause
body: <p>I am trying to get a conditional values based on count of error codes and their occurrences in specific time period from SQL query, but this query that I have written returns the first satisfied condition for all records.</p>
  
  <p>What am I doing wrong</p>
  
  <pre><code>SELECT ERROR_CODE,
         CASE
           WHEN (SELECT Count(ERROR_CODE)
                 FROM   ERROR_LOG
                 WHERE  CRET_DTIM &gt; Trunc(SYSDATE - 2)
                        AND ERROR_CODE = '50001') &gt; 50 THEN 'RED'
           WHEN (SELECT Count(ERROR_CODE)
                 FROM   ERROR_LOG
                 WHERE  CRET_DTIM &gt; Trunc(SYSDATE - 2)
                        AND ERROR_CODE = '50001') &lt; 50 THEN 'GREEN'
           WHEN (SELECT Count(ERROR_CODE)
                 FROM   ERROR_LOG
                 WHERE  CRET_DTIM &gt; Trunc(SYSDATE - 1)
                        AND ERROR_CODE IS NULL) &gt; 100 THEN 'RED'
           WHEN (SELECT Count(ERROR_CODE)
                 FROM   ERROR_LOG
                 WHERE  CRET_DTIM &gt; Trunc(SYSDATE - 1)
                        AND ERROR_CODE IS NULL) &lt; 100 THEN 'GREEN'
         END AS ALERT_COLOR
  FROM   ERROR_LOG
  GROUP  BY ERROR_CODE 
  </code></pre>
  
  <p>SAMPLE DATA</p>
  
  <pre><code>50005   05-JAN-15 11.05.51.570000000 AM
  50001   05-JAN-15 02.39.57.840000000 PM
  50001   05-JAN-15 02.31.06.700000000 PM
  50001   05-JAN-15 02.21.49.350000000 PM
  50001   05-JAN-15 01.59.13.910000000 PM
  50001   05-JAN-15 01.50.05.900000000 PM
  50001   05-JAN-15 01.30.19.270000000 PM
  50001   05-JAN-15 01.11.10.510000000 PM
  50001   05-JAN-15 12.00.00.720000000 PM
          05-JAN-15 09.42.10.670000000 AM
          05-JAN-15 09.37.31.590000000 AM
  </code></pre>
  
  <p>DESIRED OUPUT (change the count condition to see the desired output)
  <strong>COUNT</strong></p>
  
  <pre><code>        2
   50001  8
   50005  1
  </code></pre>
  
  <p><strong>SQL</strong></p>
  
  <pre><code>SELECT ERROR_CODE,
         CASE
           WHEN (SELECT Count(ERROR_CODE)
                 FROM   ERROR_LOG
                 WHERE  CRET_DTIM &gt; Trunc(SYSDATE - 2)
                        AND ERROR_CODE = '50001') between 5 and 100 THEN 'RED'
           WHEN (SELECT Count(ERROR_CODE)
                 FROM   ERROR_LOG
                 WHERE  CRET_DTIM &gt; Trunc(SYSDATE - 2)
                        AND ERROR_CODE = '50001') &lt; 5 THEN 'GREEN'
           WHEN (SELECT Count(ERROR_CODE)
                 FROM   ERROR_LOG
                 WHERE  CRET_DTIM &gt; Trunc(SYSDATE - 1)
                        AND ERROR_CODE IS NULL) &gt; 100 THEN 'RED'
           WHEN (SELECT Count(ERROR_CODE)
                 FROM   ERROR_LOG
                 WHERE  CRET_DTIM &gt; Trunc(SYSDATE - 1)
                        AND ERROR_CODE IS NULL) between 5 and 100 THEN 'GREEN'
         END AS ALERT_COLOR
  FROM   ERROR_LOG
  GROUP  BY ERROR_CODE
  </code></pre>
  
  <p><em>Result</em></p>
  
  <pre><code>Error_code   Alert_color
               GREEN
  50005        ''
  50001        RED
  </code></pre>