title: Aggregate results based on case statement
body: <p>Need some assistance writing a query. Suppose I'm using a table <code>sales</code> shown here:</p>
  <pre><code>trans_id    item_number     sales 
     1            1             10
     1            11            5
     2            11            3
     2            13            4
  </code></pre>
  <p>I want my output to contain <code>trans_id</code>, total sales for the transaction and a Y/N flag
  that can be determined by this case statement: <code>CASE WHEN item_number BETWEEN 1 AND 10 THEN  'Y' ELSE 'N' END</code>.</p>
  <p>However, I need the flag to be applied at the aggregate level where if there is at least 1 item with a 'Y' flag, then the whole transaction should include a <code>'Y'</code> flag. If no items have a <code>'Y'</code> flag, then the entire transaction should be flagged <code>'N'</code>. The output should essentially look like:</p>
  <pre><code>trans_id    flag        sales
     1         'Y'          15
     2         'N'          7
  </code></pre>
  <p>The query below nearly gets me there, but I would have to use it as a subquery
  to get me the expected results. Is it possible to modify the <code>CASE</code> statement or use
  some other approach so that I can get the desired results within the same <code>SELECT</code>
  statement (i.e. not using the query below as a subquery)?</p>
  <pre><code>SELECT trans_id
    , CASE WHEN item_number BETWEEN 1 AND 10 THEN 'Y' ELSE 'N' END
    , SUM(sales)
  FROM sales
  GROUP BY trans_id, item_number
  </code></pre>