title: Multi-aggregate, Multi-Filter, Single Table SQL
body: <p>The following simulated table contains order details where <code>cust_nbr</code> represents the order number. I'm trying to find where if an order contains an <code>item_nbr</code> 90000, I need to know if the price for 90000 is greater than the sum of the other items plus tax. I have hundreds of thousands of records in this table.  I am using Teradata.</p>
  
  <pre><code>CREATE TABLE Line_Item_Details_Tbl (
      cust_nbr INT,
      trn_dt DATE,
      str_typ VARCHAR(6),
      trn_nbr INT,
      item_nbr INT,
      price DECIMAL(6,2),
      tax DECIMAL(6,2)
  );
  </code></pre>
  
  <p>Sample data:</p>
  
  <pre><code>INSERT INTO Line_Item_Details_Tbl VALUES 
    (5551, '12/22/2011',  'store', 215, 12345, 10.00, 1.25);
  INSERT INTO Line_Item_Details_Tbl VALUES 
    (5551, '12/22/2011',  'store', 215, 65715,  6.25, 0.75);
  INSERT INTO Line_Item_Details_Tbl VALUES 
    (5551, '12/22/2011',  'store', 215, 90000, 40.00, 0);
  INSERT INTO Line_Item_Details_Tbl VALUES 
    (6875, '12/10/2011', 'online', 856, 72345,  8.50, 1.00);
  INSERT INTO Line_Item_Details_Tbl VALUES 
    (6875, '12/10/2011', 'online', 856, 65715,  6.25, 0.75);
  INSERT INTO Line_Item_Details_Tbl VALUES 
    (3500, '12/12/2011',  'store', 402, 54123, 45.00, 4.00);
  INSERT INTO Line_Item_Details_Tbl VALUES 
    (3500, '12/12/2011',  'store', 402, 90000, 20.00, 0);
  INSERT INTO Line_Item_Details_Tbl VALUES 
  </code></pre>
  
  <p>The query should do the following:</p>
  
  <pre><code>Select cust_nbr, trn_dt, trn_nbr, sum(price + tax) as purchase
    For a cust_nbr with str_typ  = 'store' AND contains an item_nbr = 90000,
    aggregate price + tax for all items related to cust_nbr except item_nbr 90000
  </code></pre>
  
  <p>So, preliminary result should be:                       </p>
  
  <pre><code>cust_nbr  :   trn_dt   : trn_nbr : purchase
  5551        12/22/2011   215       $18.25           
  3500        12/12/2011   402       $49.00           
  </code></pre>
  
  <p>Then, for each record in the preliminary results, I need to subtract the price of <code>item_nbr</code> 90000 from the <code>purchase</code> and return results only if the purchase is less than 
  the price of <code>item_nbr</code> 90000 as <code>net_cb</code></p>
  
  <p>So, my ending result should be:                     </p>
  
  <pre><code>cust_nbr  trn_dt       trn_nbr  net_cb
    5551    12/22/2011   215      ($21.75)            
  </code></pre>