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.doug@...>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.gable@...
> >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.gable@...>
> > 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]