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<[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
