General information
Category:
Coding, syntax & commands
>>>hi all :
>>>
>>>Hi was trying to make a column with the total's of the other two and paste the result to the next row .... more or less like this:
>>>
>>>Col a - Col b = total
>>>10 0 10
>>>0 15 -5
>>>
>>>Etc...
>>>Can i do This with a SQL ? I tryed to use :
>>>SELECT a,b, SUM(a+b) as total
>>>but it always gives me just one record (with -5)!
>>>
>>>Thanks in Advance !
>>
>>Don't use the SUM(), SUM() will always go an aggregate based on either ALL RECORDS, or a given group by...
>>
>>select a, b, a-b as total from mytable...
>
>
>First , Thanks for your reply .
>
>But That only give me (a-b) for current record .
>What i would like to get is (a-b) from the previous record plus (a-b) from the current record.
>EX:
>10-1 = 9
>10-5 (= 5+9) = 14
The reason why you are only getting one record is because you are not using a GROUP BY in your select statement, so it SUMS the entire table and leaves you with one record.
If you have a unique field like a Primary key (not certain but maybe the record number?), and you really want to have a SUM total, you could always to the following and do two select statements.
SELECT CustomerID, SUM(a) AS Sum_A, SUM(b) AS Sum_B FROM TableName ORDER BY CustomerID GROUP BY CustomerID INTO CURSOR cTotalData
* Second select statement to get you totals
SELECT CustomerID, Sum_A, Sum_B, Sum_A - Sum_B FROM cTotalData INTO CURSOR cFinal
Regards
Mark
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only