sum(is_complete) is only the same as "where is_complete = 1" when there is a check constraint guaranteeing that is complete will either be a 0 or 1. Dennis Cote <[EMAIL PROTECTED]> wrote: Clark Christensen wrote: > I have a table, as described below, where I need to find out if the tech_id > in question has at least some modules in a particular collection (coll_id), > and they're all complete. > > At this point, I'm working with variations on: > > select > (select count(*) from tech_modules > where tech_id = ? and coll_id = ?) as mcount, > (select count(*) from tech_modules where tech_id = ? > and coll_id = ? and is_complete = 1) as mcomplete; > > Performing the comparisons on mcount and mcomplete in the app ($mcount > 0 && > $mcomplete == $mcount) to get a boolean $allComplete. > > It performs OK (~50K rows in the table), using the unique index for both > subqueries, but the SQL seems crude. Anybody have a more elegant solution to > share? > > Thanks! > > -Clark > > CREATE TABLE TECH_MODULES ( > TECH_ID integer, > MODULE_ID integer, > COLL_ID integer, > IS_COMPLETE integer default 0, > COMPLETION_TIME date, > COMPLETION_TARGET date, > DELETED integer > ); > > CREATE INDEX tech_modules_by_module_id ON TECH_MODULES (module_id); > > CREATE UNIQUE INDEX tech_modules_unique_module_id on TECH_MODULES (tech_id, > coll_id, module_id); > > > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- > > > Clark,
You could try this: select count(*) as mcount, sum(is_complete) as mcomplete from tech_modules where tech_id = ? and coll_id = ?; HTH Dennis Cote ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
