Igor Tandetnik was right on this. Thank you. > Scalar Subqueries > > A SELECT statement enclosed in parentheses may appear as a scalar > quantity. A SELECT used as a scalar quantity must return a result set > with a single column. The result of the expression is the value of the > only column in the first row returned by the SELECT statement. If the > SELECT yields more than one result row, all rows after the first are > ignored. If the SELECT yields no rows, then the value of the > expression is NULL.
I solved my problem with a JOIN. JOIN (SELECT class, COUNT(term) AS cnt FROM (SELECT DISTINCT class, term FROM registrations GROUP BY class, term) GROUP BY class) USING (class) rather than the original sub-SELECT: (SELECT COUNT(term) FROM (SELECT DISTINCT class, term FROM registrations AS r2 GROUP BY r2.class, r2.term ORDER BY r2.class, r2.term) GROUP BY class) AS cnt So from my original: > I don't know if it is (in order of probability) 1. My ignorance, 2. > SQLite bug, or 3. SQL problem. I now know it was #1. And this is closed. On 2/5/2011 11:12 AM, Igor Tandetnik wrote: > Jeff Hennick<sqli...@jeff-h.com> wrote: >> I am having problems with Sub-Select apparently working on one row >> rather than the whole table. > When syntactically used as an expression (e.g. in SELECT clause), a subselect > produces a single value. When used as a table (e.g. in FROM clause), it may > produce multiple rows. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users