INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

a question on select sql

a question on select sql

(OP)
Hi all,
i have this select sql as below

CODE -->

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

RE: a question on select sql

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

Perhaps Like this:

CODE

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 Smileing

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.

RE: a question on select sql

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.

RE: a question on select sql

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

CODE

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 Smileing

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.

RE: a question on select sql

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.

RE: a question on select sql

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 Smileing

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.

RE: a question on select sql

(OP)
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

RE: a question on select sql

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

Regards

Griff
Keep Smileing

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.

RE: a question on select sql

(OP)
i am grouping by serialno

CODE -->

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 

RE: a question on select sql

Quote:

"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

RE: a question on select sql

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

CODE

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 Smileing

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.

RE: a question on select sql

Just tested it on a receipt details table thus:

CODE

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 Smileing

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.

RE: a question on select sql

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

RE: a question on select sql

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

CODE -->

]
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

RE: a question on select sql

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.

RE: a question on select sql

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

CODE

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 Smileing

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.

RE: a question on select sql

(OP)
ok everyone thanks for the help
i got working as it

CODE -->

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 

RE: a question on select sql

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.

RE: a question on select sql

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

CODE

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 Smileing

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.

RE: a question on select sql

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

Tamar

RE: a question on select sql

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

Regards

Griff
Keep Smileing

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.

RE: a question on select sql

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

RE: a question on select sql

Try it Mike,

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

CODE --> fails

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

CODE --> works

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 Smileing

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.

RE: a question on select sql

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.

CODE --> VFP

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 SET("EngineBehavior")

RE: a question on select sql

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

RE: a question on select sql

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

My bad.

Regards

Griff
Keep Smileing

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.

RE: a question on select sql

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

Tamar

RE: a question on select sql

sorry

Regards

Griff
Keep Smileing

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close