Boris Popov <[EMAIL PROTECTED]> wrote:
> Don't go too far, check out http://www.sqlite.org/lang.html
The BNF syntax in the SQLite webpages allows this construction:
SELECT count(a) AS b, a FROM t WHERE b > 1;
sql-statement : "SELECT" ... result ["FROM" table-list] "WHERE" expr ... ;
result : result-column[, result-column]* ;
result-column : "*" | table-name ".*" | expr [ ["AS"] string ] ;
expr : ...
| column-name
| ...
| function-name ( expr-list | * )
| ...
;
http://www.sqlite.org/lang_expr.html says:
"Aggregate functions may only be used in a SELECT statement."
I can't see anything that indicates that "AS" is forbidden to be
used with aggregate functions. Up to the WHERE seems semantically
acceptable. Therefore, I guess that the error lies in the WHERE
clause itself. Obviously I must not understand how it works.
The GROUP BY paragraph in http://www.sqlite.org/lang_select.html
indicates that perhaps the correct way to express what I was trying
to accomplish (examining logs to locate dhcp leases of more than
one ip to a mac) should have been more like:
create table dhcp (ip, mac);
select count(ip) as b from t group by mac having b>1;
But this is no longer sqlite-specific.
Thus I need to find an SQL reference (or textbook)... 8^)
-jonathan
--
Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK
<[EMAIL PROTECTED]> | systems mangler | tel/fax: +44 1223 767091/330508
"respondeo etsi mutabor" --Rosenstock-Huessy
