title: EXISTS inside CASE inside AGGREGATE FUNCTION
body: <p>I'm trying to do something similar to this:</p>
  
  <pre><code>SELECT SUM(
            CASE WHEN (&lt;some_condition&gt; 
                       AND EXISTS(SELECT 1 
                                  FROM &lt;tableA&gt; as tA
                                  WHERE tA.Id = tB.tAId and
                                  &lt;some_other_condition&gt;
                                 )
                      ) THEN 1 ELSE 0 END
            ) as &lt;column_name&gt;
  FROM &lt;tableB&gt; as tB
  </code></pre>
  
  <p>I need to avoid the use of joins to achieve what I need, because I don't want to count/sum duplicates returned by the results I get through join clauses in this case. When I try to execute my query, I get the following error message:</p>
  
  <blockquote>
    <p>"cannot perform an aggregate function on an expression containing an aggregate or a subquery"</p>
  </blockquote>
  
  <p>I don't know what I am doing wrong.</p>
  
  <p>Any help appreciated.</p>
  
  <blockquote>
    <p>EDIT: I added a possible condition on the WHERE statement only to show I need to relate the columns of the outside table (tableB) with the inside table (tableA)</p>
  </blockquote>
  
  <p>Here's an example of tableA and tableB and what happens when I JOIN both:</p>
  
  <pre><code>tableA:     tableB:          tableA JOIN tableB
  Id          Id tAId Time     Id(A) Id(B) taId Time
   1           1    1    5        1     1     1    5
   2           2    2    6        2     2     2    6
   3           3    2   10        2     3     2   10
   4           4    3   11        3     4     3   11
   5           5    4   14        4     5     4   14
               6    4   13        4     6     4   13
               7    4   12        4     7     4   12   
               8    5   10        5     8     5   10
  </code></pre>
  
  <p>So, when I try to count/sum some property (i.e. tableA.Id) from tableA using the results of the JOIN operation as input, I end up counting duplicates. Here's why I can't use JOIN unless I can select DISTINCT elements. I liked the answer that shows this so I'm trying to implement that with no errors. </p>
  
  <p>(Note: I really appreciate all the other answers since they show possible solutions that can fit my situation)</p>