title: not a single-group function in case statement
body: <pre><code>tab1
  ID_P , SYN
   1      0
   2      0
   2      1
   2      0
  
  tab2
  ID_P , SYN
   2      1
   2      1
   4      0
  
  select CASE WHEN COUNT(SYN) &gt; 1 THEN '0' 
              WHEN COUNT(SYN) = 0 AND SYN &gt; 0 THEN '0' 
              ELSE '1' END 
  from (
  select COUNT(SYN) as SYN
  from tab1
  where ID_P = 2 and SYN &lt;&gt; '0' 
  union
  select COUNT(SYN) as SYN
  from tab2
  where ID_P = 2 and SYN &lt;&gt; '0' ) 
  </code></pre>
  
  <p>What i want to verify is</p>
  
  <ol>
  <li>if the returned result from the unions have more than one row then return '0' </li>
  <li>if the returned result from the unions just contain one row and the only result is bigger that zero then return '0'</li>
  </ol>
  
  <p>The problem occurs int the second statement, 
  ORA-00937 not a single-group group function , How can i overcome this problem ?
  The result should be zero because there are rows containing '1'.
  Thanks in advance 
  Best regards </p>