SUM over column based on distinct row groups selected for another column

Question

Have a table (in SQLITE DB) with columns:

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) );

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.

NOTE : every unique SAMPNAME has the same SAMPSIZE for all rows in the table.

Had tried:

 select count(distinct SAMPNAME), sum(SAMPSIZE), TC_ID from TEST_TABLE group by TC_ID

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)

Get the required results if I do the following:

select DISTINCT TC_ID from TEST_TABLE

and then for each DISTINCT TC_ID, call

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)

Is it possible to achieve the above in a single SQLite SQL query?

(SQLite doesn't seem to support OVER/PARTITION BY constructs unfortunately.)

Hope the question is clear enough..

EDIT:

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?

Thanks in advance!

CL. CL. · Accepted Answer · 2013-07-02T12:24:25

Use a correlated subquery:

select count(distinct SAMPNAME),
       (select sum(ss)
        from (select distinct SAMPNAME,
                              SAMPSIZE as ss
              from TEST_TABLE as T2
              where T2.TC_ID = T1.TC_ID)
       ),
       TC_ID
from TEST_TABLE as T1
group by TC_ID

SUM over column based on distinct row groups selected for another column

1 Answers