Re: [sqlite] Sub-Select reports only row one value?
Jeff Hennick 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. > registrations table: > Class | Term | (Other student columns) > CC123 | 101 | ... > ... > > Desired ultimate output counts by class and term, with totals and > averages by row and by column: SQL is not well suited for building pivot tables. Its resultset is a table with a fixed number of columns and a variable number of rows. I suggest you write a query that produces a table with three columns (Class, Term, SomeNumber), and process it into a pivot table in your application code. > But when I use it as a Sub-Select (or in a Join), it gives, for ALL > rows, the result from the FIRST row. > > SELECT > r.class, > r.term, > COUNT(*) AS c, > > (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 > > FROM registrations AS r > GROUP BY r.class, r.term > ORDER BY r.class > > All rows get the same value for cnt. The subselect has no dependencies on the current row from "registrations AS r" table. It could as well be calculated once, and the resulting value inserted into every row. In what way do you expect the value to vary? What exactly was this subselect supposed to achieve? It makes no sense to me. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sub-Select reports only row one value?
Igor, Thank you for your consideration and reply. On 2/5/2011 11:12 AM, Igor Tandetnik wrote: > Jeff Hennick wrote: > 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. This may be my problem. I "knew" it, but was thinking "one column", not "one row." I'm going to have to do some more thinking on this. My first approach, before a sub-Select, had been a self-Join. Maybe I'll have to create a temporary table and Join it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sub-Select reports only row one value?
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 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
