totalising figures for seperate records

[T17Rax]

Hi again everyone,

I'm trying to achieve a way to find the total spend for everyone that's bought things over a month.
using the Sum(Am1) expression works by giving me the entire column, however that appears as a grand total for the entire month.
What I'm after is a total of say "Mr. Smith" and another total for "Mrs Smith".

Am1 holds the amount spent
Name holds the name
Accno holds the account number.
Cardno holds the account number too in a different table.
Balance will be the field that will hold the total value of amount spent per user.

So basically when the name or account number changes, that's when VFP should totalise the figures for that user.

Code below so far (I've re-written it five different times!):

CLOSE TABLES ALL 
USE id IN 4
USE till IN 1
USE temptable IN 2
SELECT 1
SET FILTER TO dat =>{^2015/11/02} AND dat <={^2015/11/16}
SORT TO november ON accno /A

USE november IN 3
DO WHILE !EOF() = .T.
CALCULATE SUM(Am1) WHILE till.accno = id.cardno
INSERT INTO temptable (balance) VALUES (Am1)
LOOP
SKIP
ENDDO

I was hoping to try and crack it but clearly not yet lol...
Eventually this will be integrated into a form with buttons for a end user to scroll through - but that's another chapter!

Thanks guys.

A conclusion is simply a place where you got tired of thinking.

 

[Olaf Doschke]

SQL group by

To divide the amounts spent for mr and mrs smith you obviously need another ID on top of the accountID, a personID.

[tt]Select Sum(Am1), Year(dat), Month(dat), accno, personid From till Group by 2,3,4,5 Into Cursor curTotalsperMonthAccountAndPerson[/tt]

If Mr and Mrs smith have different accno, then you can do with the fields already mentioned in your code:

[tt]Select Sum(Am1), Year(dat), Month(dat), accno From till Group by 2,3,4 Into Cursor curTotalsperMonthAndAccount[/tt]

Bye, Olaf.

 

[Mike Lewis]

Balance will be the field that will hold the total value of amount spent per user.

If Balance is a field in the same table as Am1, Name, Accno, etc., then you can't really do what you want. That's because each record in the table represents one transaction, and it doesn't make sense to place a total of multiple transactions against a single transaction.

Balance really needs to be in a separate table, in which case you can use Olaf's solution to generate it.

In fact, in "pure" database design, you wouldn't store the Balance at all, but would calculate it as needed, for example, when producing a report.

Miket

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Mike,

While that's true from the redundancy perspective I think the reality differs even for commercial POS systems.

I'd think a balance would be part of a register table, though, not within a till table. Additional to that the till data would confirm the balance changes to be correct, though you can always falsify both data in the same manner and not detect some fraud. If both data of the balance and the summed till amounts confirms the same total, but not the physical count of the registers cash, you'd rather have a cashier "lending" some money, but a mismatch of all three values would leave you with doubts about what is correct.

If you really would change your system to only store the till amounts as per Mikes advice, there's more that can be done to verify correctness of the data, eg calculating checksums for till records. That would compare to an electronic signature of each transaction, though after changing till amounts someone knowing the checksum algorithm would also easily make a change of that. Especially it would be inappropriate to compute the checksum automatic via triggers or field or table rules, as that would automatically adjust it even for the dumb hacker just adjusting the till amounts and balance. On top of that it would put the needed code into the database visible for anyone.

Bye, Olaf.

 

[Mike Lewis]

Well, I wasn't really advocating the "pure" solution in this case - merely pointing out that it exists. But I'm a little puzzled about why he is keeping a balance against each transaction. Is it a running total perhaps?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

VibrantSeeker,

May I point out a few issues with your code? I realise that you probably won't be using this code in the end, but I hope the following points will help you to understand certain issues.

1. Never use work area numbers; use meaningful aliases instead. So instead of this:

[tt]USE id IN 4
USE till IN 1
USE temptable IN 2
SELECT 1[/tt]

do this:

[tt]USE id IN 0
USE till IN 0
USE temptable IN 0
SELECT till
etc.[/tt]

2. [tt]SET FILTER TO dat =>{^2015/11/02} AND dat <={^2015/11/16}[/tt]

The syntax is OK, but it's a little neater to say [tt]SET FILTER TO BETWEEN(dat, {^2015/11/02}, {^2015/11/16} )[/tt].

3. [tt]SORT TO november ON accno /A[/tt]

Again, the syntax is OK, but SORT is very rarely used. It is much more efficient to maintain an index. And you don't need to recreate the index each time it is used. Just create it once, and it will always be up to date.

4. Instead of DO WHILE, use SCAN / ENDSCAN. In other words, instead of this:

[tt]DO WHILE !EOF() = .T.
...
SKIP
ENDDO[/tt]

you can do this:

[tt]SCAN
...
ENDSCAN[/tt]

Also, it's not at all clear why you have LOOP here. The LOOP transfers control back to the DO WHILE, which means the SKIP will never be executed, which is surely not what you want.

5. I any case, why are you doing CALCULATE within a loop, when you are already using a WHILE clause to limit its scope? Also, CALCULATE without a TO clause is useless, as there is nowhere to store the result.

I hope you find these points useful.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Good points, I didn't address the sample code at all, as a group by query will replace all the code with just the query, that could be adapted to your needs, eg querying INTO TABLE or APPENDing the result cursor into some table. Anyway it does not createa running total, the loop also does not, only puts the overall total into a table.

As far as I see the code you'd only loop once, since you end at EOF after the first CALCULATE WHILE anyway. The CALCULATE itself processes all records fullfilling the condition and skips towards EOF. Otherwise you'd have an endless loop, as Mike correclty observes you always do LOOP before SKIP, so you never reach SKIP. The CALCULATE already is processing all records of a certain account in one line.

Bye, Olaf.

 

[T17Rax]

Hi Guys,

Apologies for the delay in response.

The principle of the code was to gather a user's transactions and give a total, for a date range. i.e, a month. A live balance is kept in a completely seperate table.
It was an idea perhaps that had come into my mind where a user may want to see a total spend-to-date of someone in the system.
I very much doubt that this code would be used live in anyway shape or form - for the time being I'm practicing where I can, whenever I can.
My code was just something I thought of - thinking it must work in a logical way somehow.

I really appreciate both inputs here:
Olaf - that line worked wonders and I can see how it sums up totals for individual months. I never had thought Group By worked in that way.
Mike, Your tips are very very helpful and I will probably read over them again and again! I'm hoping one day I'll get the grasp of VFP.

Vibrantseeker.



A conclusion is simply a place where you got tired of thinking.

 

[Dan Freeman]

since you end at EOF after the first CALCULATE WHILE anyway

You might accidentally end up at EOF() after the calculate, but with a scope of WHILE it isn't automatic. WHILE will stop when a record not matching the scope is encountered. The scope would need to be FOR to guarantee a trip to EOF(). <pedant />

 

[Olaf Doschke]

Dan, that actually is my understanding of the WHILE scope, but then the typical situation would be not ending at EOF and since the code worked I stopped thinking about that. REST FOR always ends at EOF, WHILE does stop at the first non matching record.

The CALCULATE has a weird condition, as it's about till and id, not about the new november table. This condition either is .T. or .F. no matter what record of november is processed. So either there are additional code lines or a relation or this just worked by chance.

Vibrantseeker, glad the query does what you need. Yes, SQL often enough is complex, but it would be a bad language, if it couldn't do some useful things in simple queries. It's a language meant in the direction of natural language query code, that should make it easy to read and understand. SQL always is worth learning, as it can summarize much loop code into a single query and also performs well with indexes.

Bye, Olaf.

 

[T17Rax]

Olaf,

If you don't mind me asking, how does "Group By 2,3,4,5" fetch the fields and name them into the cursor? As the date fields for year and month come under "Exp_2" and "Exp_3" yet the rest appear to have the orginal names from till table.

Thanks,
Vibrantseeker.



A conclusion is simply a place where you got tired of thinking.

 

[Olaf Doschke]

Well, 2,3,4,5 are just the field numbers. It's not just coincidentally the EXP_N also are having the field numbers, VFP ensures no double field names this way and it creates these names as fields have to have some name.

You can specify a field name of your like with AS, eg SUM(Am1) AS Totalamount, it'll still be the first column. The group by is easiest with just field numbers, no matre if the field is an expression or not.

Bye, Olaf.

 

[Olaf Doschke]

A more natural order of data may be account nummber first, then the year/month and the total:

[tt]Select accno, Year(dat) AS yr , Month(dat) AS mon, Sum(Am1) AS tot From till Group by 1,2,3 && Into Cursor curAccountTotalsperMonth[/tt]

And by the way, the AS keyword is optional.

Bye, Olaf.

 

[Mike Lewis]

And by the way, the AS keyword is optional

That's right. And, as with most optional keywords, it's a good idea to include it, as it makes the meaning much clearer to someone who is not familiar with the syntax.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[T17Rax]

Olaf,

So am I right in thinking that if you give a name to a field by referencing "Select Year(dat) as yr", the numbers just ensure the position of the field?
Ha ha I hadn't thought about the order of data. It was something I put together to try and get something going. If this was going live however, it would definitely be a more natural and professional layout.
Vibrantseeker

A conclusion is simply a place where you got tired of thinking.

 

[Mike Lewis]

Using an AS clause is, in a sense, an alternative to using numbers in a GROUP BY clause.

So, you could do this:

[tt]Select Sum(Am1), Year(dat), ... accno From till Group by 2, ...[/tt]

OR you could do this:

[tt]Select Sum(Am1), Year(dat) AS Year_Tot, ... From till Group by Year_Tot, ...[/tt]

They both give the same result. Choose whichever you feel more comfortable with (personally, I would go with the second option).

By the way, I am not saying that can't use numbers in GROUP BY and the AS clause. I'm just saying that, in this context, they serve a similar goal.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

The Group By is not specifying where fields go, it's specifying which fields to group by, so that is opposite of what you think.

Additional to Mikes answer the order of fields doesn't really play a role, as you can easily specify control sources of grid columns in any other order or put report controls in whatever suitable order, no matter which is field1,2,3, etc.

If you change order of fields in the field list, you have to change numbers or names in group by accordingly. The single group is determined by same values in all mentioned fields, so the order of field numbers or names also doesn't matter, GROUP BY 2,3,4 has the same groupd as GROUP BY 3,4,2 etc. but you may think of drilling down from general to more specific, eg its natural to group data by year first, then by month, not vice versa, but the tuple of year/month is the same as the tuple month/year, only if you'd shorten the groupsing and ONLY group by month without grouping by year at the same time, you'd get weird totalss of all amounts spent in any January of all years. Does that make sense? Such groupings would perhaps make sense for metrological data determining a january climate, but not in businesss figures.

That said, if you group by yr,mon,accno you can arrange the fields in the field list as you like the group by wouldn't need to change. Anyway, both the field list and group by clause is in one place and the field number syntax is the shorter. In other SQL dialects you have other rules, eg T-SQL doesn't accept the field numbers, but on the other side also not alias names, on the other side you can even group by expressions you don't put into the select field list.

Bye, Olaf.

 

[T17Rax]

Olaf, Mike,

Gotcha! I understand it now.
Thanks for all your help and advice with this. As I said, it's just practice and messing about - trying to get used to VFP but it's great to know that the more I learn with the help from you guys, the more I'll know.

Thanks once again. I'm sure I'll be back haha.

Vibrantseeker

A conclusion is simply a place where you got tired of thinking.