×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# TO MAKE PRG WITH TOTALS AND CASE 3

## TO MAKE PRG WITH TOTALS AND CASE

(OP)
In ATTACHED table cmontds
I require sum of quantity of itemcode=”1”,”2”,”4”
Say in the giving table it will be 5000+321000+4000=330000 (SUMMS)
And sum of quantity of itemcode=”4”
Which will be in the given table as 302000 (SUMHSD)
NOW
SUMTOTQTY = SUM(SUMMS+SUMHSD)
now do case starts................

as per attached word

will be highly obliged if someone can give the prg to be written so that i can use and try to understand

thanks

regards

### RE: TO MAKE PRG WITH TOTALS AND CASE

Hi 007,

Could you please post the code you tried to achieve your goal.

hth

MarK

### RE: TO MAKE PRG WITH TOTALS AND CASE

I'm afraid your post doesn't make much sense (at least, not to me). It's not clear exactly what you are trying to achieve. It doesn't help that you expect us to download a zip file to understand the problem.

Please try to explain the problem in simple language.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

### RE: TO MAKE PRG WITH TOTALS AND CASE

(OP)
Tore Bleken sorry Sir did not mean to just in the flow it was added

sir mjcmkrsr
sir Mike Lewis
attached herewith is screenshots of goals to be achieved from the table
and then steptwo required
please the same has to be grouped according to monthno into a table

forgive me if i could not explain the same in a better way earlier

thanks
regards

### RE: TO MAKE PRG WITH TOTALS AND CASE

As far as I can understand you want always filter some defined items from your file and summarize their quantity.

You can test or code it like that:

SUM(quantity) to group1 for itemcode = 1 or itemcode = 2 or itemcode = 6
? group1 ***This is the 330000 in your sample.
SUM (quantity) TO group2 FOR itemcode = 4
? group2 *** this is the 302000 in your sample
SUM quantity TO grouptotal
? grouptotal
groupleft = grouptotal- group1-group2 * This is the difference for all other items which where not summarized.
? groupleft

Regards
Klaus

Peace worldwide - it starts here...

### RE: TO MAKE PRG WITH TOTALS AND CASE

2
Just some food for thought:

#### CODE

Create Cursor sales (itemcode char(2), quantity currency)
Insert into sales values ("3",  $999.95) Insert into sales values ("15",$573.49)
Insert into sales values ("32",  $12.99) Insert into sales values ("15",$219.90)
Insert into sales values ("32",  $25.98) Insert into sales values ("3",$199.95)

clear
Set Console on
Sum to variable for itemcode="3"
Sum to variable for itemcode="3 " 

Only the second sum command gives the expected result. Why?

#### CODE

Select itemcode, sum(quantity) as total From sales group by itemcode

That SQL work and gives you totals for all itemcodes. It has no surprises of mis-grouping data. It can also be limited to only certain itemcodes by a WHERE clause, for example.

Chriss

### RE: TO MAKE PRG WITH TOTALS AND CASE

Chriss
This is interesting that when summing values, VFP obviously only recognizes the 1st letter of a string (itemcode is a string) when assigning groups. I think I would have fallen for that. I haven't found an exact explanation as to why this is the case.
But it stands out when I do this:
x="3"
y = "3 "
? VAL(x)
? VAL(y)
In the display, the value of y is shifted one place to the right. This is also the case with string addition.
3.00 (for x)
. 3.00 (for y)

This is also the case with string addition.
x + y would result in "33" here.
But there are certainly better explanations.
In any case, the example is a good warning of how easy it is to mismap something when working with strings.
Thanks for the hint.
It's definitely safer to order with the value of strings.

Klaus

Peace worldwide - it starts here...

### RE: TO MAKE PRG WITH TOTALS AND CASE

Hi Klaus,

the answer to why is in the help topics about SET EXACT and SET ANSI ON/OFF or more generally the way VFP compares strings and evaluates them as equal or different. At default VFP only compares partial strings, so that "32"="3" is true, but at the same time it's not transient (you cannot swap and get the same boolean result), "3"="32" is false.

But even without knowing these details, there is a simple way to see why group by does not fail: The group by is done by the whole field, while code like SUM FOR itemcode="3" a) doesn't group for all itemcodes but just picks out the sum for itemcode "3" and b) isn't the full length itemcode "3 ". Whereas group by taking the full field length it groups by "3 " and "32" separately and those strings definitely differ no matter how strings are compared when they have different lengths, all strings in char fields by definition have the same length and when the padding spaces are taken into account, you don't misgroup.

That's why SUM ... FOR itemcode="3 " then works. Or whatever length itemcode has.

You can, indeed, reintroduce the same problem into SQL when you add a where clause that compares with a partial itemcode.

#### CODE

Select itemcode, sum(quantity) as total From sales group by itemcode where itemcode="3"

Doing that you also get the sums for both itemcode "3" and "32", but SQL saves your back, if you look into the result:

The where clause also fetches both itmecodes, so you get more than you expect, but group by makes two groups from the data and you still get the group you actually wanted and you can understand your comparison isn't as specifically picking out only one itemcode but all itemcodes starting with "3".

And the essence of it all, only use constant length identifiers when using strings. And, well, you could also say better not use strings for identifiers, but numbers. Comparison of numbers is more straight forward for what is equal, though floating point also has many gotchas in that respect.

The reason string comparisons work that weirdly also is easy to explain, as VFP is mainly for data processing and within databases before the introduction of variable length fields ou still wanted to find some Mr. Smith by writing WHERE lastname="Smith" and not WHERE lastname = PADR("Smith", len(lastname)) or even worse manually padding with spaces which would be code that only works as long as the field has the same length.

Think alone of the fact that SQL was meant to be nearer to natural language than usual code. Then it would sabotage that purpose if the user of SQL has to think about such details in the queries. VFP by default differs from how the ANSI standardization of SQL defined string comparisons, with SET ANSI ON you can also use the "too simple" where clause and only get one group. Same SQL, just after SET ANSI ON:

Is it therefore recommended to SET ANSI ON? It depends. But there are good reasons for VFP to differ from the ANSI standard in that respect. So it's not a bugfix that you can turn on or off, it's a design decision that goes into aspects of how the indexes are organized and rushmore optimization, I guess, too.

Chriss

### RE: TO MAKE PRG WITH TOTALS AND CASE

(OP)
thank you sirs i added for itemcode="1 " and "2 " got my result
thanks again
regards

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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!