Something more, generally it is a better practice to have a view and call
that view.

So, you would write: "SELECT * FROM MyView WHERE MyCondition GROUP BY
MyColumns ORDER BY MyOrder"

Greetings.

Walter.




On Sat, Jun 15, 2013 at 3:54 PM, Iwan Cahyadi Sugeng <
iwan.c.sugeng@...> wrote:

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