Funky GROUP BY

[Ruslan Zasukhin]

On 10/11/06 7:05 PM, "Balestrieri, John" <jbalestrieri at ap.org> wrote:

> Hello, I have group by clause that's not working as expected when I run it
> from Valentina Studio 2.4.3:
> 
> SELECT Race.SeatNumber, Race.OfficeID, Race.Meta_State,
> Sum(Results.VoteCount)
> FROM Results, Race Group BY Race.SeatNumber
> 
> The error is:
> 
> ----
> 11:55:50: Error: Kernel error: 0x42508
> 11:55:50: Error: In the SELECT clause you can use only fields listed in the
> GROUP BY and aggregative functions.
> ----
> 
> So following the error to correct the 'problem', things work if I provide
> all the fields from the select statement, as in the statement below:
> 
> SELECT Race.SeatNumber, Race.OfficeID, Race.Meta_State,
> Sum(Results.VoteCount)
> FROM Results, Race Group BY Race.SeatNumber,
> Race.OfficeID, Race.Meta_State, Results.VoteCount

No need list all fields! Only That fields that are not under aggregative
functions:

SELECT 
    Race.SeatNumber,
    Race.OfficeID, 
    Race.Meta_State,
    Sum(Results.VoteCount)
FROM 
    Results JOIN Race
Group BY 
    Race.SeatNumber, Race.OfficeID, Race.Meta_State


> I've used the first select statement before, and variations of it, in other
> SQL engines as well as long ago in Valentina 1.x without any problems. Also,
> the Valentina doc wiki shows a similar example:

John, Valentina 2 follows to SQL92 VERY STRICTLY.

 
> SELECT rep, AVG (amount) FROM orders GROUP BY rep

It is not the same!

SELECT have 2 fields:
    amount -- under AVG
    rep    -- listed in GROUP BY.
 
So all right here.

> <http://www.valentina-db.com/dokuwiki/doku.php?id=paradigma:public:en:docume
> ntation:vsql:reference:select:clustering_queries&s=group>
> 
> Where only one field is referenced in the GROUP BY clause. I've got a HUGE
> select statement in my actual code, and repeating every field in the GROUP
> BY is very cumbersome. Is there a shortcut? Any help figuring this out would
> be appreciated.

Again, please read carefully error message.


-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]