Funky GROUP BY

[Ruslan Zasukhin]

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

Hi John,

> OK, moving forward slowly... I apologize for bombarding the list like
> this... I only deal with DB issues about once every two years... if this is
> not the correct list, please let me know.
> 
> So, I'm expanding my original query, and have a new error, and want to make
> sure I understand it correctly before modifying my database.
> 
> The query:
> 
> SELECT Race.SeatNumber, Race.OfficeID, Candidate.CandidateID,
> Results.CandidateID, Race.RaceCountyID, 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
> 
> The error:
> -----
> 13:01:57: Error: Kernel error: 0x80501
> 13:01:57: Error: Field name "CandidateID" is not unique.
> ------
 
> As far as I can tell, the error with the duplicate field is in the GROUP BY
> section, where there are collisions between the field names between
> different fields once they've been GROUPED.

Hmm, sounds like a bug.
Should not be collision because you specify Table name.

Please try as workaround assign to one of these fields alias:

SELECT Race.SeatNumber, Race.OfficeID,
       Candidate.CandidateID AS 'CCID',
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
       Results.CandidateID, Race.RaceCountyID,
       Results.RaceCountyID,
       Race.Meta_State,
       Sum(Results.VoteCount)

.....

>  I've unsuccessfully tried column
> name aliases as well referencing (once) the CandidateID column by column
> name alone (not as tablename.columnname).

Note, that column alias should be in SINGLE QUOTES

> In this case, is the only solution
> to change the name of the original columns when I create the tables, to
> avoid duplicate names when the tables are GROUP BYed?

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