Sami-- where've you been all my life...you're wonderful!
was getting past ebing frustrated. tom ----- Original Message ----- From: "Sami Aaron" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> Sent: Thursday, August 19, 2004 4:19 PM Subject: [RBG7-L] - Re: illegal column specification?????? > Tom - > > You're getting close ... > > Now change AVERAGE to AVG > and > > MAXIMUM to MAX > > and see what you get. > > Sami > ------------------------------------------ > Sami Aaron > Software Management Specialists > 19312 W 63rd Terr > Shawnee KS 66218 > 913-915-1971 > mailto:[EMAIL PROTECTED] > www.softwaremgmt.com > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Thomas > > Eldred > > Sent: Thursday, August 19, 2004 2:56 PM > > To: RBG7-L Mailing List > > Subject: [RBG7-L] - Re: illegal column specification?????? > > > > > > Thank you Larry and Albert, > > > > I have fixed the missing plus and enclosed my expresion in a min() so that > > it looked both as MIN((.....)) and MIN(...) > > > > I am still getting a syntax error. > > > > Code now reads(for all three tables) > > > > CREATE VIEW electcommrben + > > (typecount, employtype, + > > avgchair, avgvc,avgmem, avgcommr, minboard, + > > maxboard, avgboard) + > > AS SELECT + > > COUNT(*),MIN((selectionmethod+' '+ service)) , + > > AVERAGE(chairmansalary) , + > > AVERAGE(vicechairsalary) , + > > AVERAGE(membersalary) ,+ > > AVERAGE((chairmansalary+vicechairsalary+membersalary)/3), + > > MIN(chairmansalary+vicechairsalary+membersalary), + > > MAXIMUM(chairmansalary+vicechairsalary+membersalary), + > > AVERAGE(chairmansalary+vicechairsalary+membersalary) + > > FROM miscommrs WHERE mcyear = 2002 AND selectionmethod = 'elected' AND > > service = 'Part time' > > > > Thank you again > > > > tom > > > > ----- Original Message ----- > > From: "Lawrence Lustig" <[EMAIL PROTECTED]> > > To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> > > Sent: Thursday, August 19, 2004 2:51 PM > > Subject: [RBG7-L] - Re: illegal column specification?????? > > > > > > > The problem is that in each view you are including a > > > single non-aggregated return value (the second field, > > > which is a concatenation of two columns in the > > > database). > > > > > > If you include aggregate and non-aggregate fields in > > > your result set you must include a GROUP BY clause in > > > the select including all non-aggregate fields. > > > > > > In your case, since I think you're after a single row > > > in your result set limited by the WHERE clause, just > > > remove the non-aggregate expression or, if you must > > > have it, encase it in a call to MIN(). > > > > > > -- > > > Larry > > > > > > > > > > > > > > > >
