Information générale
Catégorie:
Codage, syntaxe et commandes
>>>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
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement