I am having problems with Sub-Select apparently working on one row
rather than the whole table.
registrations table:
Class | Term | (Other student columns)
CC123 | 101 | ...
CC002 | 101
CC050 | 111
CC123 | 101
CC123 | 102
...
Desired ultimate output counts by class and term, with totals and
averages by row and by column:
Class | 093 | 101 | 102 | 103 | 111 | Total | Average
CC001 | 10 | 8 | 12 | 7 | 8 | 45 | 9
CC002 | 6 | 11 | | 13 | | 30 | 10
... | | | | | | |
CC278 | | 6 | 8 | | | 14 | 7
Totals: | ...
I want to be able to use Order By on any column, including Average.
The first columns aren't too difficult. But Averages within a row (or
even Counts to calculate an average) has me baffled.
I don't know if it is (in order of probability) 1. My ignorance, 2.
SQLite bug, or 3. SQL problem.
When I use this not so pretty Select, I can get good counts of non-NULL
terms:
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
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. I can verify that it is getting
the cnt from the first row by introducing a class "CC000" with just one
student. Using a self-Join rather than sub-Select has the same result.
Environments: 1. SQLite embedded in PHP5 called via PDO (Windows or
FreeBSD), 2. SQLite Manager addon for Firefox. Same results from each.
Apparently the optimization is going overboard. I have, as you see,
tried to help defeat it using aliases on the two uses of the table.
My workaround, when the Order By is the averages column, is to post
process in PHP which involves a full array and sorting it. Kind of
defeats the use of SQL.
Any help or insights would be most welcomed. Thank you.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
