EXISTS inside CASE inside AGGREGATE FUNCTION
<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>