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

 

[TamarGranor]

Just want to add that ORDER BY is NOT required in order to use GROUP BY.

Tamar

 

[GriffMG]

I think it might be in this case, VFP throws an error if you leave it out

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. ​

 

[Mike Lewis]

I can't see why leaving out the ORDER BY would cause an error. As far as I know, the only time an ORDER BY is compulsory is with TOP N.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Try it Mike,

I don't know 'why' mike, but if I leave out the order by, VFP 9 throws an error

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

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

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]

Griff

Both of your statements should fail because you're selecting a column (item_no) that is not part of the GROUP BY clause. Unless you're not working with the VFP9 engine.

CREATE CURSOR Test (ColumnOne Integer, ColumnTwo Integer)

INSERT INTO Test VALUES (1, 2)
INSERT INTO Test VALUES (1, 3)
INSERT INTO Test VALUES (1, 4)
INSERT INTO Test VALUES (2, 1)

SELECT SUM(ColumnTwo) AS Aggregated, "Value" AS Constant, ColumnOne AS GroupedBy ;
	FROM Test ;
	GROUP BY ColumnOne

* working as it should, no ORDER BY required

What error does VFP raise when you execute your ORDERless statement?

And what is the value of [tt]SET("EngineBehavior")[/tt]

 

[Mike Lewis]

Griff, I'll certainly try that code (modified to fit some actual data I have available).

But at first glance, it looks like it will fail in any case with a "GROUP BY missing or invalid". The problem is the item_no in the expression list, which violates the rule that the expressions have to be either aggretates or the subject of a GROUP BY.

I'll give it a try and report back.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

Quite right gentlemen, it was the itemno that was throwing the error, not the lack of order by

My bad.

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. ​

 

[TamarGranor]

And just a reminder that "gentlemen" is not the appropriate term here. I'm a woman.

Tamar

 

[GriffMG]

sorry

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. ​