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