Subject RE: [firebird-support] Advice with the following sql command
Author Andrew Gable
Post date 2014-09-25T14:11:21Z

Ok so how do I sort it?

Select TRANS_DATE,TRANSTIME, POSNUMBER, TRANSNUMBER, Count(ITEM_QTY) as ItemsSold, SUM(LINE_TOTAL) as LineTotal from accounthistory "
WHERE TRANS_DATE BETWEEN '" & StartDate & "' AND '" & EndDate AND AccountID='1001'
GROUP BY ItemsSold, LineTotal Order By TRANS_DATE, TRANSTIME DESC;

Is that how I would sort it?

 

From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: 25 September 2014 3:09 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Advice with the following sql command

 




Means exactly what it says. You don't have TRANS_DATE and TRANSTIME in either the GROUP BY or in an aggregate function.

As the query can process many records with different values for these columns what do you expect the query to return?

On 25/09/2014 14:38, 'Andrew Gable' andrew.gable@... [firebird-support] wrote:

 

Hi all,

I need someone please to advice me of the following sql code

 

Select TRANS_DATE,TRANSTIME, POSNUMBER, TRANSNUMBER, Count(ITEM_QTY) as ItemsSold,

SUM(LINE_TOTAL) as LineTotal from accounthistory "

WHERE TRANS_DATE BETWEEN '" & StartDate & "' AND '" & EndDate AND AccountID='1001'

GROUP BY POSNUMBER, TRANSNUMBER Order By TRANS_DATE, TRANSTIME DESC;

 

When I run that within my application I get this error message

 

Run Time Error -2147467259 (80004005)


[ODBC Firebird Driver][Firebird] SQL Error
SQL error code =-104
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

 

Any advice is welcomed as this has me stumped as well as my SQL expert mate

 

Thanks

Andy




-- 
Tim Ward