Re: [firebird-support] Advice with the following sql command

2014-09-25 Thread Tim Ward t...@telensa.com [firebird-support] 
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.ga...@tesco.net 
[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

RE: [firebird-support] Advice with the following sql command

2014-09-25 Thread 'Andrew Gable' andrew.ga...@tesco.net [firebird-support] 
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.ga...@tesco.net
[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

RE: [firebird-support] Advice with the following sql command

2014-09-25 Thread 'Bogdan Mihalache' bogdan.mihala...@soter.ro [firebird-support] 
Manyou 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: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, September 25, 2014 5:11 PM
To: firebird-support@yahoogroups.com
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: firebird-support@yahoogroups.com
mailto:firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 25 September 2014 3:09 PM
To: firebird-support@yahoogroups.com
mailto: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.ga...@tesco.net
mailto:andrew.ga...@tesco.net  [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

RE: [firebird-support] Advice with the following sql command

2014-09-25 Thread 'Andrew Gable' andrew.ga...@tesco.net [firebird-support] 
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: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: 25 September 2014 3:33 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Advice with the following sql command

 






Manyou 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: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, September 25, 2014 5:11 PM
To: firebird-support@yahoogroups.com
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: 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.ga...@tesco.net
[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

Re: [firebird-support] Advice with the following sql command

2014-09-25 Thread W O sistemas2000profesio...@gmail.com [firebird-support] 
If a column in a SELECT list is not an aggregate function then you need to
write it in the GROUP BY clause.

Many people have your same problem when start using SQL.

Greetings.

Walter.



On Thu, Sep 25, 2014 at 9:38 AM, 'Andrew Gable' andrew.ga...@tesco.net
[firebird-support] firebird-support@yahoogroups.com 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