Help with SQL-Syntax (in VFP)

[Dan Olsson]

Hi, folks, hope you are having a good time in the holidays. I probably only have a brain freeze but I can't solve this just now .
I have a table where I need to sum some fields from our version of a SSN (Personal Identification Number).
Something like this (simplified):

SELECT SSN, SUM(salary) FROM ALIAS() GROUP BY 1

Easy right? The problem is that the SSN can be in more than one row in the table and the result doesn't combine them to one row (and one sum). I'm sure I have done this before, but just now I can't for my life think of the right syntax. I'm sure though that you can

 

[Chris Miller]

Well, for clarification I'd write

SELECT SSN, SUM(salary) as Totalsalary FROM atable GROUP BY SSN INTO CURSOR curSalaryTotals

Selecting from the current workarea with ALIAS() is no error, but are you sure the right workarea is selected?
GROUP BY column number also is okay, so all in all, if you don't get a SUM per SSN, take a closer look at the SSNs in the result, they'll differ, even if only in litttle aspects.

Or do you look into the ALIAS() afterward?
If you don't specify INTO CURSOR result that'll be a cursor called "Query", and it will also pop up right away.

Chriss

 

[Chris Miller]

A blatant error you could do, you query from the employee table, which defines the salary and has a record per person, so there are not multiple records per employee, the query you do would make sense on a payroll/payments table, when you do this table by monthly adding the current salary and use the SSN also as a foreign key in that payroll data. But if it fails on that level, working on the wrong table, you really will need to know your database better.

First of all find out what number of records you have, or add it to the query:

SELECT SSN, COUNT(*) as count, SUM(salary) as Totalsalary FROM atable GROUP BY SSN INTO CURSOR curSalaryTotals

Perhps expecting 12 as the count.

Chriss

 

[Mike Lewis]

Dan, could you clarify what you mean by "the SSN can be in more than one row in the table". I assume you don't mean that multiple rows can have the same SSN, as that surely is a given - otherwise you wouldn't need the GROUP BY.

Perhaps a (very short) example of the relevant data would help.

and I completely endorse what Chris says about using ALIAS() in this context. That's asking for trouble.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

I assume you don't mean that multiple rows can have the same SSN, as that surely is a given - otherwise you wouldn't need the GROUP BY.

I'm not sure what you wanted to point out. But Dan is correct in saying the SSN can be in more than one row in the table, otherwise SUM() wouldn't be necessary to sum salaries from multiple rows. The GROUP BY becomes obsolete and not only that, also absurd, if you'd use it in a table where each SSN is unique, then you could simply SELECT SSN,salary FROM thattable, group by won't sum up anything. So a SUM only makes sense in a table where each group defined by the groop column or columns has more than 1 record.

Dan, as you say

the result doesn't combine them to one row (and one sum)

It does, that is what GROUP BY does. And that will work, no matter whether you applied this query to the wrong table or not. You'll not have two rows with the sme SSN in the query result and the sum column will be a sum. I'm not sure why you say this, either because you see a SSN twice, then you don't look into the query result, or the sums you see are too low. Well, then you applied the query to the wrong table, as I already assumed.

I assume you want to total the paid salaries, well the SSN and salary field surely could exist in an employee table, where you wouldn't have multiple records with the same SSN, for sure, and so the query result would just be the current employees salary, not the years total, which likely is 12 times that. So you'd just query the wrong table.

Overall I wonder why this is a problem, as you surely did this kind of end-of-the year bilancing each year. I assume the query is part of a program that does more than just this summary of paid salaries. Well, any change you made likely affected the current alias that this query uses by using ALIAS() instead of a concrete table name. Now you see why it is not a good choice to do that. In most cases that would lead to an error, as any other than the table you actually need to address would have the necessary fields in it, but in case of swapping a payroll table using SSNs to refer to an employee and storing the monthly salary in a field also called salary the employee table could have the same fields with only one record per SSN, so that kind of error is possible, the query works for both the payroll and the employee table and doesn't cause system error, but an unexpected result.

It's a reason to think about using ALIAS(), you don't want an error of selecting the wrong workarea lead to unuseful and wrong results, even though it doesn't error, technically. So if you want a payroll summary, better specify the payroll table and not just ALIAS().

I guess you solved the problem by now. If not, that's surely solvable as aftermath next year, so best wishes for that and overall for the next year. Perhaps make it a new years resolution to think over any query on ALIAS(). A query always is meant for a specific table. The only reason I could think of using ALIAS() would be in case this query is run on a table that's splitting data into mutiple tables, the construct would then requery to use all the partial data tables and then select them one after the other and call this query. You'd still need to total all single results, in such a case, as the query would only have totals for each part of the overall data.

If that's the situation and the reason may not even be the 2GB limit but splitting up data by years or by quarters or some other partitioning criterion, well, you cornered yourself into an overcomplicated data structuring that makes bilancing harder, not easier.

I'm sure you'll figure it out.

Chriss

 

[Dan Olsson]

Thank you all for taking your time to answer in the holidays and all
I said i had simplified the example and unfortunately I did it a little to much.
In reality I don't use ALIAS() and I of course SELECT into a named cursor, but nothing of that was the problem.
The problem was in a WHERE clause that I didn't show you because I thought you shouldn't get the meaning of it.
Sorry I wasted your time and happy holidays for y'all