Group BY: what's wrong with this?

[Frank Schima]

On Mar 31, 2005, at 3:39 PM, michael geary wrote:

> using V4MD:
>
> sql = select authors.* from authors GROUP BY authors.email ORDER BY 
> authors.lastName asc
>
> results in the error:
> "In the SELECT clause you can use only fields listed in the GROUP BY 
> and aggregative functions."
>
> In MySQL this query works just fine. What am I doing wrong in 
> Valentina?

While MySQL does return a result, it is hardly valid. You are asking 
for one value for each group for each field when there can be many due 
to the GROUP BY. Which one to return? MySQL seems to return the first 
value, but I fail to see how that means anything. In fact, I think it 
is an error in MySQL.

When you use GROUP BY, you must only select the GROUP BY fields.

    select authors.lastName from authors GROUP BY authors.email ORDER BY 
authors.lastName asc

You can select aggregate functions too.

    select authors.lastName, sum(amount), count(*) from authors GROUP BY 
authors.email ORDER BY authors.lastName asc

After all, that is the purpose of GROUP BY. If you wanted to select all 
fields, then surely you must want all values and hence why are you 
using GROUP BY in the first place?


HTH,
Frank Schima