Funky GROUP BY

[Ivan Smahin]

Hello,

Wednesday, October 11, 2006, 8:51:55 PM, you wrote:

> On 10/11/06 8:42 PM, "Balestrieri, John" <jbalestrieri ÎÁ ap.org> wrote:

>> Almost there...
>> 
>> SELECT Race.SeatNumber, Race.OfficeID, Candidate.CandidateID AS 'CCID',
>> 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, CCID, Results.CandidateID,
>> Race.RaceCountyID, Results.RaceCountyID, Race.Meta_State, Results.VoteCount
>> 
>> ORDER BY Race.Seatnumber, Results.VoteCount DESC
>> 
>> When I reference the alias CCID in the GROUP BY clause, the error is:
>> 
>> -----
>> 13:37:30: Error: Kernel error: 0x51500
>> 13:37:30: Error: Identifier "CCID" is not found.
>> -----
>> 
>> So it looks like the alias creation is OK not throwing an error, but the
>> referencing of it is throwing an error.

> Ivan will check now and will give answer.


Let me explain using simple example.

CREATE TABLE t1 ( f1 LONG )
CREATE TABLE t2 ( f1 LONG )

-- Workable example.
--

SELECT
      t1.f1  as 'a',
      t2.f1  as 'b'
FROM
    t1, t2
WHERE
     t1.f1 = t2.f1
GROUP BY
      t1.f1,
      t2.f1

--

1. "... GROUP BY a, b" does not work because aggregation operates with
"original" fields, but tables have no fields with such names.

2.  "SELECT  t1.f1,  t2.f1  ...  GROUP  BY t1.f1, t2.f1" does not work
because  aggregation  creates  new  table with fields listed in select
clause. And both fields have to be named "f1" here - so you get an exception.


-- 
Best regards,
 Ivan                            mailto:ivan_smahin ÎÁ valentina-db.com