title: Effective way To use Count and JOIN in PRESTO CASE statements
body: <p>This is on back of a question raised earlier on <a href="https://stackoverflow.com/questions/62873767/effective-way-in-presto-to-result-output-with-boolean-values"><code>Effective way in PRESTO to Result output with boolen values</code></a>. Please refer the question for more details.</p>
  <p>On back of the question two solutions are availabe.</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT  dp.USER_NAME,
          dp.ID, 
          CASE 
              WHEN dp.sex='F' THEN 'True' 
              ELSE 'False' 
          END AS Rule_1,
          CASE 
              WHEN dp.sex='M' THEN 'True' 
              ELSE 'False' 
          END AS Rule_2,
          CASE 
              WHEN dp.sex not in ('M','F') THEN 'True' 
              ELSE 'False' 
          END AS Rule_3
  FROM    user_details dp where dp.Organisation='007';
  </code></pre>
  <pre class="lang-sql prettyprint-override"><code>SELECT dp.USER_NAME, dp.ID, 
         (dp.sex = 'F') AS Rule_1,
         (dp.sex = 'M') AS Rule_2,
         (dp.sex not in ('M','F')) AS Rule_3
  FROM user_details dp 
  WHERE dp.Organisation = '007';
  </code></pre>
  <p><strong>Questions:</strong></p>
  <p>With CASE I cant able to use <code>COUNT</code> how can i add a rule if i want to see COUNT is greater than 2. (Count(*) &gt; 0) = True else False with JOIN Tables to the selected Query?</p>
  <p>Is there a way to user <code>COUNT</code> in case?</p>
  <p>What im trying to create:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT dp.USER_NAME, dp. ID, 
         (dp.sex = 'F') AS Rule_1,
         (dp.sex = 'M') AS Rule_2,
         (dp.sex not in ('M','F')) AS Rule_3,
         (dp.DateOfBirth NOT BETWEEN timestamp '1986-04-01' AND timestamp '2001-03-31') AS Rule_4,
         (count(up.*)&gt;10) AS Rule_5,
  FROM user_details dp 
  JOIN user_purchase up on up.user_id=dp.id
  where dp.Organisation='007';
  </code></pre>
  <p>In the above query i'm trying to post a Boolean value if the <code>user_purchase</code> table last data &gt; 10, that particular row output value should be <code>True</code> else <code>False</code>.</p>
  <pre><code>USER_NAME   |  ID | Rule 1 | Rule 2 | Rule 3 | Rule 4 | Rule 5
  
  user1          1     true    false    true     true     true
  
  user2          2     false   true     true     true     true
  
  user3          3     true    true     true     true     false
  </code></pre>