Funky GROUP BY

[Ruslan Zasukhin]

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

> Also, the rows
> are not grouping properly, meaning all the Race.SeatNumber items are not
> being aggregated together... Back to the drawing board on this one for me...

This sounds not good.

1) please try REINDEX database and see if this fix problem.

2) if not helps ... Then may be we need check your db

 
> SELECT Race.SeatNumber, Race.OfficeID, Candidate.CandidateID AS 'CCID',
> Results.CandidateID, Race.RaceCountyID as 'RRID', Results.RaceCountyID,
> Race.Meta_State, Sum(Results.VoteCount)
> 
> FROM    Results, Candidate, Race WHERE Results.CandidateID =
> Candidate.CandidateID and Race.RaceCountyID = Results.RaceCountyID and
> Race.Meta_State='CA' and Race.OfficeID = 'H'
> 
> GROUP BY Race.SeatNumber, Race.OfficeID, Candidate.CandidateID,
> Results.CandidateID, Race.RaceCountyID, Results.RaceCountyID,
> Race.Meta_State
> 
> ORDER BY Race.Seatnumber, Results.VoteCount DESC

Btw, about ORDER BY.

Race.Seatnumber -- present in GROUP BY, and even as FIRST field.
    so records already sorted by this field.


Results.VoteCount -- PROBLEM it seems.
    I even wonder, why Valentina keep silent on this...
    By SQL92 you cannot ORDER BY on field which not in SELECT.

    Field Results.VoteCount is not in SELECT.
    in SELECT present SUM(Results.VoteCount)

May be you want sort by result of SUM() ?

Then you need write:

SELECT .....
        SUM(Results.VoteCount) AS 'VotesTotal'

......

ORDER BY VotesTotal



-- 
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]