title: Grouping twice by different criteria, same column
body: <p>I have data with the following columns: </p>
  
  <pre><code>OFFICER_ID, CLIENT_ID, SECURITY_CODE, POSITION_SIZE 
  </code></pre>
  
  <p>and then per each row for instance:</p>
  
  <pre><code>officer1, client100, securityZYX, $100k,
  officer2, client124, securityADF, $200k,
  officer1, client130, securityARR, $150k,
  officer4, client452, securityADF, $200k,
  officer2, client124, securityARR, $500k,
  officer7, client108, securityZYX, $223k, 
  and so on.
  </code></pre>
  
  <p>As you see, each client has a single officer assigned to either buy o sell securities, but each client can have bought different securities.</p>
  
  <p>Apart from ranking officers by total amount in US$ of securities held by their clients (which I've done) I need to create ranges of total client accounts by adding total securities held by client ID, for example, total securities held sum &lt; $1million, between $1million and $3million and > $3 million.</p>
  
  <p>I've tried:</p>
  
  <pre><code>SELECT officer_ID, SUM(position_size) as AUM
  FROM trades
  GROUP BY client_ID
  HAVING AUM &gt; 1000000 AND AUM &lt; 3000000; 
  </code></pre>
  
  <p>and I get a list of all officers appearing several times, no totals.</p>
  
  <p>I'd need a simple:</p>
  
  <pre><code>Officer_ID | range &lt; 1m | range 1m-3m | range &gt; 3m
  </code></pre>
  
  <p>officer1, [total amount of client accounts with securities adding up &lt; 1m totals], [total amount of client accounts with securities adding up between 1m and 3m totals], etc.</p>
  
  <p>Please, would you point me in the right direction?</p>
  
  <p>UPDATE</p>
  
  <p>I modified Tim's suggested code and obtained the desired output:</p>
  
  <pre><code>SELECT
      OFFICER_ID,
      SUM(CASE WHEN total &lt; 1000000 THEN total END) AS "range &lt; 1m",
      SUM(CASE WHEN total &gt;= 1000000 AND total &lt; 3000000 THEN total END) AS "range 1m-3m",
      SUM(CASE WHEN total &gt;= 3000000 THEN total END) AS "range &gt; 3m"
  FROM
  (
      SELECT OFFICER_ID, CLIENT_ID, SUM(POSITION_SIZE) AS total
      FROM trades
      GROUP BY OFFICER_ID, CLIENT_ID
  ) t
  GROUP BY
      OFFICER_ID;
  </code></pre>
  
  <p>Too kind, Tim, thanks!</p>