title: Teradata - Cannot nest aggregate operations
body: <p>The PROD_AMT I'd like to get is when ACCT_NBR, PROD_NBR And PROD_AMT are the same, I only need one PROD_AMT which is 100 (from distinct), and when ACCT_NBR are the same but PROD_NBR are different, then the PROD_AMT I need is 90 (30+60)</p>
  
  <pre><code>SELECT ACCT_NBR
  ,COUNT(DISTINCT CASE WHEN PROD_NBR = 1 THEN SUM(DISTINCT PROD_AMT)
                       WHEN PROD_NBR &gt; 1 THEN SUM(PROD_AMT)
                  END) AS AMT
  FROM TABLE
  
  ACCT_NBR PROD_NBR PROD_AMT
  3007     001      30
  3007     002      60
  1000     003      100
  1000     003      100
  </code></pre>