Change it into this syntax: 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 PRODUCTODE, PRODUCTDESCRIPTION " SQLCommand = SQLCommand & "ORDER BY PRODUCTQTY DESC " SQLCommand = SQLCommand & "ROWS 1 TO 25" On Sun, Jun 16, 2013 at 2:51 AM, Doug Chamberlin <chamberlin.d...@gmail.com>wrote: > ** > > > 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] > > > -- Iwan Cahyadi Sugeng Interaktif Cipta Lestari [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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
