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