title: SUM over column based on distinct row groups selected for another column
body: <p>Have a table (in SQLITE DB) with columns:</p>
  
  <pre><code>CREATE TABLE TEST_TABLE(TC_ID INTEGER NOT NULL, CONF_ID INTEGER NOT NULL, SAMPNAME TEXT NOT NULL, SAMPSIZE INTEGER NOT NULL, PRIMARY KEY(TC_ID, CONF_ID, SAMPNAME) );
  </code></pre>
  
  <p>Need to be able to count all unique(DISTINCT) SAMPNAME entries for any given TC_ID (i.e. grouped by TC_ID) and and also the sum of SAMPSIZE over these unique SAMPNAME entries for every TC_ID.</p>
  
  <p><strong>NOTE</strong> : every unique SAMPNAME has the same SAMPSIZE for all rows in the table.</p>
  
  <p>Had tried:</p>
  
  <pre><code> select count(distinct SAMPNAME), sum(SAMPSIZE), TC_ID from TEST_TABLE group by TC_ID
  </code></pre>
  
  <p>Which gives expected value for the count of distinct SAMPNAMEs, but doesn't give the expected SUM for SAMPSIZEs (as it reaccounts for the SAMPSIZE when SAMPNAMEs are not distinct)</p>
  
  <p>Get the required results if I do the following:</p>
  
  <pre><code>select DISTINCT TC_ID from TEST_TABLE
  </code></pre>
  
  <p>and then for each DISTINCT TC_ID, call</p>
  
  <pre><code>select count(sn), sum(ss), TC_ID from (select distinct SAMPNAME as sn,SAMPSIZE as ss, TC_ID from TEST_TABLE where TC_ID=$currTCID)
  </code></pre>
  
  <p>Is it possible to achieve the above in a single SQLite SQL query?</p>
  
  <p>(SQLite doesn't seem to support OVER/PARTITION BY constructs unfortunately.)</p>
  
  <p>Hope the question is clear enough..</p>
  
  <p><strong>EDIT</strong>:</p>
  
  <p>While the query works fine with the SQL provided by CL (Thanks!), would additionally like to know if a different table/schema design exists which could perform a more efficient query than requiring the correlated subquery to be performed for every group/row?</p>
  
  <p>Thanks in advance!</p>