[sqlite] GROUP BY With ASC/DESC
Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) with: SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? SQLite version 3.7.2 3.7.9. danap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] GROUP BY With ASC/DESC
Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) with: SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? SQLite version 3.7.2 3.7.9. danap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY With ASC/DESC
On 11 Nov 2011, at 2:23am, dmp wrote: I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) with: SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Your query appears to be mixing simple results and aggregate results. The point of GROUP BY is to be able to do things like SUM(). But you have in your SELECT the 'name' column and there's no way to tell which name would be returned for any group. Try this statement SELECT color, Sum(price) AS sumPrice FROM key_table5 GROUP BY color If that works, gradually add components of the query and figure out which one causes the problem. I might, for example, expect uncertainty when you change it to SELECT name, color, Sum(price) AS sumPrice FROM key_table5 GROUP BY color Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY With ASC/DESC
dmp da...@ttc-cmc.net wrote: I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) with: SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 ASC is not valid with GROUP BY, only with ORDER BY. What is it supposed to achieve, anyway? Your query 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] GROUP BY With ASC/DESC
On 11 Nov 2011, at 1:15pm, Igor Tandetnik wrote: ASC is not valid with GROUP BY, only with ORDER BY. What is it supposed to achieve, anyway? Your query makes no sense to me. Igor, I started out to write exactly the same thing, but then I saw the diagram on http://www.sqlite.org/lang_select.html which suggests that ASC is legal, even if it doesn't make sense. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY With ASC/DESC
SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? Looks like a documentation bug. Indeed the select-core diagram incorrectly shows an ordering-term in the GROUP BY clause. It should probably be named grouping-term instead, with grouping-term being defined as a comma-separated list of column expressions. Others have already commented on the semantic of your query. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY With ASC/DESC
On Fri, Nov 11, 2011 at 02:24:54PM +0100, Jean-Christophe Deschamps scratched on the wall: Is this a bug or a query statement inaccuracy on my part? Looks like a documentation bug. Indeed the select-core diagram incorrectly shows an ordering-term in the GROUP BY clause. It should probably be named grouping-term instead, with grouping-term being defined as a comma-separated list of column expressions. Yes and no. The railroad diagrams are not definitive, and are meant more as an illustrative guideline, then a strict grammar. They do not directly reflect the actual parse tree used by the SQLite parser. Further, while the diagrams try to represent what the parser will accept, that may or may not (as in this case) be a logical SQL statement. Not all of the statement validation is done at the parsing level. As someone that's gone through and modified almost every diagram for republishing, I can assure you that if you start to dig into things, it is not difficult to find instances where you can use the diagrams to create statements that make no logical sense, and are not accepted by SQLite. The diagrams alone cannot keep you from avoiding errors if you do not know the language and how it works. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY With ASC/DESC
On Fri, Nov 11, 2011 at 8:20 AM, Simon Slavin slav...@bigfraud.org wrote: On 11 Nov 2011, at 1:15pm, Igor Tandetnik wrote: ASC is not valid with GROUP BY, only with ORDER BY. What is it supposed to achieve, anyway? Your query makes no sense to me. Igor, I started out to write exactly the same thing, but then I saw the diagram on http://www.sqlite.org/lang_select.html which suggests that ASC is legal, even if it doesn't make sense. Documentation bug is now fixed. http://www.sqlite.org/docsrc/info/40ef9e8de8 Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] GROUP BY With ASC/DESC
Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) with: SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? SQLite version 3.7.2 3.7.9. danap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY With ASC/DESC
I think you want ORDER BY COLOR,NAME Since color is a primary key you can't have dups so what good is the group by? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of da...@dandymadeproductions.com [da...@dandymadeproductions.com] Sent: Friday, November 11, 2011 9:57 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] GROUP BY With ASC/DESC Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) with: SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? SQLite version 3.7.2 3.7.9. danap. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GROUP BY With ASC/DESC
Message: 23 Date: Fri, 11 Nov 2011 10:57:22 -0500 From: da...@dandymadeproductions.com To: sqlite-users@sqlite.org Subject: [sqlite] GROUP BY With ASC/DESC Message-ID: 5ed601b698a020a8d790240cc05c8714.squir...@dandymadeproductions.com Content-Type: text/plain;charset=iso-8859-1 Given the following table: DROP TABLE IF EXISTS key_table5; CREATE TABLE key_table5 ( name TEXT NOT NULL default '', color TEXT default NULL, price REAL default NULL, UNIQUE (name), PRIMARY KEY (color) ); I'm getting the following exception: SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) with: SELECT name, color, Sum(price) AS price FROM key_table5 WHERE price '2' GROUP BY color ASC ORDER BY name ASC LIMIT 50 OFFSET 0 when either ASC or DESC is used with the GROUP BY clause. Is this a bug or a query statement inaccuracy on my part? SQLite version 3.7.2 3.7.9. danap. -- Message: 24 Date: Fri, 11 Nov 2011 16:10:04 + From: Black, Michael (IS) michael.bla...@ngc.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Subject: Re: [sqlite] GROUP BY With ASC/DESC Message-ID: ABF72471-0160-4FBE-A249-5CFBC96DD19B@mimectl Content-Type: text/plain; charset=iso-8859-1 I think you want ORDER BY COLOR,NAME Since color is a primary key you can't have dups so what good is the group by? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems Currently I'm implementing an interface for the MyJSQLView SQL GUI to use the GROUP BY aspect of SQL statements. Upon testing with SQLite I received the above indicated exeception. So the context given is only an example with no predefined objective of meaningful results. Perhaps a more meanful example with the same results: Given: ROP TABLE IF EXISTS General_Expenses; CREATE TABLE General_Expenses ( id INTEGER NOT NULL, date date NOT NULL, company TEXT NOT NULL, record_type TEXT NOT NULL, payment_method TEXT NOT NULL, account TEXT NOT NULL, description TEXT, cost REAL NOT NULL, PRIMARY KEY (id) ); The query with GROUP BY account works fine, but if ASC or DESC included with the field GROUP BY an exception is given. (fine) SELECT date, company, account, Sum(cost) AS cost FROM General_Expenses WHERE date LIKE '2011%' GROUP BY account ORDER BY account ASC (exception) SELECT date, company, account, Sum(cost) AS cost FROM General_Expenses WHERE date LIKE '2011%' GROUP BY account ASC ORDER BY account ASC SQLException: [SQLITE_ERROR] SQL error or missing database (near ASC: syntax error) SQLState: null VendorError: 0 Is this a bug, since the documentation indicates ASC and DESC can be used with GROUP BY statements? danap. Dana M. Proctor MyJSQLView Project Manager http://myjsqlview.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
