Funky GROUP BY

[Balestrieri, John]

On 10/11/06 4:04 PM, "Ruslan Zasukhin" <sunshine at public.kherson.ua> wrote:

> This sounds not good.
> 
> 1) please try REINDEX database and see if this fix problem.

Reindexing was successful, but no change in the returned results.

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

That would be much appreciated, if it is necessary. (I will have to prepare
a subset of the data -- the DB is 100mb)

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

OK... Good to know (though if it was in my original SQL, it may not have
been last time I used this code)

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

Yes, this slipped into what I pasted into my email.. I omitted it earlier
and still have the same results.

>     Field Results.VoteCount is not in SELECT.
>     in SELECT present SUM(Results.VoteCount)
> 
> May be you want sort by result of SUM() ?

Generally, intent of the my original SQL should be correct, as I use it in
the last election with no problems (different Db engine) though if Valentina
is more accurate, than perhaps the old behavior was not right even thought
the correct results were returned.

 
> Then you need write:
> 
> SELECT .....
>         SUM(Results.VoteCount) AS 'VotesTotal'
> 
> ......
> 
> ORDER BY VotesTotal


The information contained in this communication is intended for the use of the designated recipients named above. If the reader of this communication is not the intended recipient, you are hereby notified that you have received this communication in error, and that any review, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify The Associated Press immediately by telephone at +1-212-621-1898 and delete this email. Thank you.