[sqlite] GROUP BY With ASC/DESC

[danap]

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

[danap]

> 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

Re: [sqlite] GROUP BY With ASC/DESC

[Black, Michael (IS)]

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

[sqlite] GROUP BY With ASC/DESC

[danap]

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

[Richard Hipp]

On Fri, Nov 11, 2011 at 8:20 AM, Simon Slavin  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
>
> 
>
> 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

Re: [sqlite] GROUP BY With ASC/DESC

[Jay A. Kreibich]

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

[Jean-Christophe Deschamps]

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

[Simon Slavin]

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



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

[Igor Tandetnik]

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

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

[Simon Slavin]

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

[sqlite] GROUP BY With ASC/DESC

[dmp]

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