The problem is that you have asked to summarize the data by grouping on product description but you have also asked for a product code. The server does not know if there is a one-to-one relationship between product description and product code. Therefore, it does not know what product code to use for each product description and,therefore, for each row in the resulting data set.
The solution is to add product code to the GROUP BY clause. That way you will get a resulting record for every unique product code-product description combination and the server will know which product code to return because it is the one being paired with product description in that specific grouping. The rule you violated is the one that requires all requested fields in the output result set to be either the result of an aggregation function, such as SUM(), or it must appear in the GROUP BY clause (so the server will know what value to return). On Sat, Jun 15, 2013 at 3:03 PM, Andrew gable <andrew.ga...@tesco.net>wrote: > I am sorry I Really do not understand :( > > -----Original Message----- > From: firebird-support@yahoogroups.com [mailto: > firebird-support@yahoogroups.com] On Behalf Of Nagy Szilveszter > Sent: 15 June 2013 5:57 PM > To: firebird-support@yahoogroups.com > Subject: Re: [firebird-support] Help on this SQL statment > > GROUP BY PRODUCTDESCRIPTION there is PRODUCTCODE missing here > > if you use aggregate functions u have to add all fields to GROUP BY that > are not used with aggregate functions > > > ________________________________ > From: Andrew gable <andrew.ga...@tesco.net> > To: firebird-support@yahoogroups.com > Sent: Saturday, June 15, 2013 7:54 PM > Subject: [firebird-support] Help on this SQL statment > > > > > Hi everyone, > > Can someone please advise me as to what is happing with this SQL Select > statement I am trying to use > > SQLCommand = vbNullString > > SQLCommand = SQLCommand & "SELECT " > > SQLCommand = SQLCommand & "PRODUCTCODE," > > SQLCommand = SQLCommand & "PRODUCTDESCRIPTION," > > SQLCommand = SQLCommand & "SUM(PRODUCTQTY)," > > SQLCommand = SQLCommand & "SUM(PRODUCTTOTAL) " > > SQLCommand = SQLCommand & "From ProductHistory " > > SQLCommand = SQLCommand & "WHERE PRODUCTDATE Between '" & StartDate & "' > AND '" & EndDate & "' " > > SQLCommand = SQLCommand & "GROUP BY PRODUCTDESCRIPTION " > > SQLCommand = SQLCommand & "ORDER BY PRODUCTQTY DESC " > > SQLCommand = SQLCommand & "ROWS 1 TO 25" > > I am getting the following error > > -2147467259 - [ODBC Firebird Driver][Firebird]Dynamic SQL Error > > SQL error code =-104 > > Invalid expression in the select list (not contained in either an aggregate > function or the GROUP BY clause) > > If someone could explain that to me I would be most greatful. > > [Non-text portions of this message have been removed] > > > > > [Non-text portions of this message have been removed] > > > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links > > > > > > ----- > No virus found in this message. > Checked by AVG - www.avg.com > Version: 2013.0.3345 / Virus Database: 3199/6413 - Release Date: 06/15/13 > > > > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links > > > > [Non-text portions of this message have been removed]
