|
|
Posted by neutralm@gmail.com on 08/28/06 22:49
Thanks for your prompt reply, Erland. Pardon my ignorance, but I'm
still not sure if I understood how to solve the problem (although I
think I understand what the problem is from your explanation).
I have two tables:
1. tags (with the primary key 'id' and an attribute 'name')
2. taggings (the primary key is 'id', the foreign key is 'tag_id')
The query string I'm using is:
select tags.id, taggings.tag_id, name, count(*) as count from taggings,
tags where tags.id = taggings.tag_id group by taggings.tag_id
How should the correct query look like?
Thanks so much in advance!
Erland Sommarskog wrote:
> (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]
|