title: Conditional sum and count in Teradata (SQL help)
body: <p>My data is setup in the following way: </p>
  
  <pre><code>Person  Account Revenue Region
      A       W       100     AU
      A       W       200     AU
      A       W       300     AU
      B       X       200     AU
      B       X       50      CH
      B       X       50      CH
  </code></pre>
  
  <p>Here is code for the sample data:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#StackTest') IS NOT NULL
      DROP TABLE #StackTest;
  
  CREATE TABLE #StackTest
  (Person varchar(1)
  , Account varchar(1)
  , Revenue int
  , Region varchar(2));
  
  INSERT INTO #StackTest
  (Person
  , Account
  , Revenue
  , Region)
  VALUES
  ('A', 'W', 100, 'AU'),
  ('A', 'W', 200, 'AU'),
  ('A', 'W', 300, 'AU'),
  ('B', 'X', 200, 'AU'),
  ('B', 'X', 50, 'CH'),
  ('B', 'X', 50, 'CH');
  </code></pre>
  
  <p>I need to write a SQL query that sums revenue for only those accounts when total sum of an account Q exceeds Y. Similarly, I also need to count only those accounts when total sum of an account Q exceeds Y. So if my sum threshold for region AU is 500 and for region CH is 200, then I would want the following output</p>
  
  <pre><code>Output  # of accounts exceeding threshold sum   Revenue from these accounts 
  A                     1                                  600    
  B                     0                                   0 
  </code></pre>
  
  <p>However, my current query is checking each line item separately and not at the account level.</p>
  
  <p>What should I do?</p>