a question on select sql 3

[Ernesto_Paez]

Hi all,
i have this select sql as below

Select	"067006432" As RoutTrtno ,  "2000014857496" As ACCOUNT ,checkno As  SerialNo, checkdate As IssueDte, Aprpay As Amount ,;
	"320" As Trstype,  Company As Company ;
	FROM APCHCK04 ;
	WHERE checkdate Between m.ldFrom And m.ldto Order By 4  Into Cursor ApexRecord Readwrite

the checkno in the cursor can appears several times with the same value but the Aprpay, will be different, so i would like just grab the checkno and summarize the Aprpay for that checkno, so i can send this to an excel as just one row and the total amount for those 3 records in the cursor( i said 3 records as an example), instead of, for example 3 rows with the same checkno and the amount for each of that checkno.
Thanks a lot in advance

 

[GriffMG]

I think you would need a sum() and a group to do that

Perhaps Like this:

Select	"067006432" As RoutTrtno ,  "2000014857496" As ACCOUNT ,checkno As  SerialNo, checkdate As IssueDte, Sum(Aprpay) As Amount ,;
	"320" As Trstype,  Company As Company ;
	FROM APCHCK04 ;
	WHERE checkdate Between m.ldFrom And m.ldto group by SerialNo  Into Cursor ApexRecord Readwrite

Not 100% sure about the Sum(Aprpay) as Amount...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

You're selecting constant values for most columns without filtering the underlying data, I don't think that is a good idea. Griff has a point to summarize you need to group by something, but by what can only be a guess, it rather seems to me you want to group by all the fields you set as constant value and only retrieve one account. Make use of the where clause.

Bye, Olaf.

 

[GriffMG]

If checkno is generally unique (except to a series of aprpays) you might be able to simplify this somewhat

Select	checkno As  SerialNo, Sum(Aprpay) As Amount ;
	FROM APCHCK04 ;
	WHERE checkdate Between m.ldFrom And m.ldto group by SerialNo  Into Cursor ApexRecord Readwrite

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Olaf Doschke]

You need something like WHERE ACCOUNT = "2000014857496" AND... instead of "2000014857496" As ACCOUNT, the way you do it you always get "2000014857496" As ACCOUNT, even if the stored account number differs, so you pull data from other accounts, where you only want data from one.

Bye, Olaf.

 

[GriffMG]

I think that's deliberate Olaf, he is trying to prepopulate the columns ready to export to excel I reckon.

I suspect he would be better off simplifying for test purposes and then add the other bits he seems to need.

i.e. get it to work first!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Ernesto_Paez]

Hi,
why when i run this by group, i get this error
"GROUP BY CLAUSE is missing or invalid "

the reason i have those fixed values in the select as below
"Select "067006432" As RoutTrtno , "2000014857496" As ACCOUNT"
it is cause they are going to be always the same, so instead of using

"Replace RoutTrtno with "067006432" all in ApexRecord" , i just pit in there, now my problem is on the error by group
Thanks in advance for any suggestion

 

[GriffMG]

What are you grouping by, can you post the SQL statement

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Ernesto_Paez]

i am grouping by serialno

Select	"067006432" As RoutTrtno ,  "2000014857496" As ACCOUNT ,checkno As  SerialNo, checkdate As IssueDte, Sum(Aprpay) As Amount ,;
	"320" As Trstype,  Company As Company ;
	FROM APCHCK04 ;
	WHERE checkdate Between m.ldFrom And m.ldto group by SerialNo  Into Cursor ApexRecord Readwrite

 

[Mike Lewis]

"GROUP BY CLAUSE is missing or invalid "

The error message means what it says.

In general, whenever you have an aggregate function - in this case, your SUM() function - then any other expressions in the result set must be the subject of a grouping. In your case, you don't have a GROUP BY clause, so it will try to group by the entire table. In other words, it will give you a single record, in which the Amount column is the total of all the Arppays for the entire table.

The trouble with that is that it won't know what to put in the Serialno column. Clearly you want a Checkno, but it has no way of knowing from which record to take the Checkno. Hence the error.

This should be easy to fix, but you are in a better position to do that than we are, because you know the structure of your data and what you want to achieve.

By the way, I think Griff's point about leaving out the constants was simply to help you understand the problem and work out the solution. In other words, simplify your code so that you just focus on the problem. You can always come back to deal with the constants later.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

I think he needs add an order by so that the group by can work

Select	"067006432" As RoutTrtno ,  "2000014857496" As ACCOUNT ,checkno As  SerialNo, checkdate As IssueDte, Sum(Aprpay) As Amount ,;
	"320" As Trstype,  Company As Company ;
	FROM APCHCK04 ;
	WHERE checkdate Between m.ldFrom And m.ldto order by SerialNo group by SerialNo  Into Cursor ApexRecord Readwrite

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

Just tested it on a receipt details table thus:

SELECT receipt_no, SUM(total) as amount FROM recpdetl ORDER BY receipt_no GROUP BY receipt_no

And that works a treat

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[atlopes]

Landfla, in what version of VFP are you running this code?

 

[Ernesto_Paez]

VFP 9.0
if do not include the others fields it works
for example the code below will work

]
Select	checkno As  SerialNo, Sum(Aprpay) As Amount ;
	FROM APCHCK04 ;
	WHERE checkdate Between m.ldFrom And m.ldto group by SerialNo  Into Cursor ApexRecord Readwrite 
[/code

but i need the rest of the fields in the cursor and in that particular position,  as i am sending this to excel and later send it to the bank in that particular structure  order.

 i am very confused with  "GROUP BY CLAUSE"
Thanks a lot,  i don't know if i have to include each field in the "group by clause"[code]

, i doubt it

 

[atlopes]

In VFP9, you can't have a query that includes GROUP BY clause and columns which are neither grouped, nor constants, nor the result of an aggregate function.

That is, "aprpay" column should be changed into SUM(Aprpay), as Griff already mentioned, but the other data columns - checkdate and company - should also be turned into aggregated columns, or included in the GROUP BY clause.

 

[GriffMG]

I don't think you do, because this works too:

SELECT receipt_no, SUM(total) as amount, "123" as MyCode, "XYZ" as MyOtherCode FROM recpdetl ORDER BY receipt_no GROUP BY receipt_no

That is in VFP9 and works in VFP6 too

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Ernesto_Paez]

ok everyone thanks for the help
i got working as it

Select	"067006432" As RoutTrtno ,  "2000014857496" As ACCOUNT ,checkno As  SerialNo, checkdate As IssueDte, Sum(Aprpay) As Amount ,;
	"320" As Trstype,  Company As Company ;
	FROM APCHCK04 ;
	WHERE checkdate Between m.ldFrom And m.ldto order by SerialNo group BY RoutTrtno , ACCOUNT,SerialNo,IssueDte,Trstype, Company Into Cursor ApexRecord Readwrite

 

[atlopes]

Griff, you have a column which is part of the GROUP BY clause (receipt_no), the result of an aggregate function (amount), and two constants (MyCode and MyOtherCode). What you can't have, as Landfla does, is columns which are none of these (checkdate and Company).

To be part of the result query, they may a) be turned into aggregated columns, such as MIN(checkdate); or b) be included in the GROUP BY clause.

 

[Ernesto_Paez]

Here is the result excel file from the cursor
Thanks to everyone

 

[GriffMG]

You are quite right - the use of a constant is an aggregate function!

Select	"067006432" As RoutTrtno ,  "2000014857496" As ACCOUNT ,checkno As  SerialNo, checkdate As IssueDte, Sum(Aprpay) As Amount ,;
	"320" As Trstype,  Company FROM APCHCK04 ;
	WHERE checkdate Between m.ldFrom And m.ldto order by SerialNo group BY SerialNo, IssueDte, Company Into Cursor ApexRecord Readwrite

Should probably work

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​