|
|
Posted by Erland Sommarskog on 08/28/06 21:31
(neutralm@gmail.com) writes:
> I am getting the following error:
>
> OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
> Column 'tags.id' is invalid in the select list because it is not
> contained in either an aggregate function or the GROUP BY clause.
>
>
> when trying to execute the following query:
>
> select tags.id, name, count(*) as count from taggings, tags where
> tags.id = tag_id group by tag_id
>
>
> The above query works fine on MySQL, but chokes on SQL Server.
SQL Server, like most DB engines, as well as ANSI SQL, that if your
SELECT list includes an aggregate such as COUNT(*), and there is no
OVER clause for the aggregate, then all unaggregated columns in the
SELECT list must appear in the GROUP BY list.
Change tag_id in the GROUP BY clause to tags.id or vice versa.
Apparently MySQL is lax on this point. As a matter of fact SQL Server
4.x also permitted columns to appear in the SELECT list, if they did
not appear in GROUP BY. Sometimes the result made sense, as here
where tags.id is one-to-one with tags_id. Sometimes you got screenfulls
of garbage when you expected two lines, because you had left out a
column in the GROUP BY clause. The feature was removed in SQL Server
6.0 (and Sybase System 10), missed by few.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|