> 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 +0000 > 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
