Thanks Everyone with your advice and example code I have worked out what I have wrong
Thank You all I can now work on updating my software to use the new function J From: [email protected] [mailto:[email protected]] Sent: 25 September 2014 3:33 PM To: [email protected] Subject: RE: [firebird-support] Advice with the following sql command Man....you shlould read a bit more before going on this road. It a simple example of select with group option. 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 TRANS_DATE, TRANSTIME, POSNUMBER, TRANSNUMBER Order By TRANS_DATE, TRANSTIME DESC And at least...do you know what you want from that select? Are you sure that you il will suit your needs ? Good luck! From: [email protected] [mailto:[email protected]] Sent: Thursday, September 25, 2014 5:11 PM To: [email protected] Subject: RE: [firebird-support] Advice with the following sql command 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: [email protected] [mailto:[email protected]] Sent: 25 September 2014 3:09 PM To: [email protected] 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' [email protected] [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
