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. > 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
