Information générale
Catégorie:
Base de données, Tables, Vues, Index et syntaxe SQL
>Hello,
>I need to make a SELECT where i can show the movements and the totals together, like this:
>SELECT cod,name,deb,cre,SUM(totdeb-totcre) from tablex
>
>the TOTDEB-TOTCRE are a SUM and the SUM commmand returns only one record, i dont want that, i want all records, like this:
>
>|cod| name | deb | cre | Sald |
>-------------------------------
>| 1 | aaaa | 10 | 0 | 10 |
>| 2 | aaaa | 0 | 15 | -5 |
>...
>Urgent please, thanks
>Rui Trol
Running total queries use a non-equi-self-join, and preliminary queries to get the records in order and number them:
SELECT *, deb-cre AS balance FROM tablex INTO CURSOR cur1 NOFILTER ORDER BY whatever
SELECT *,RECNO() AS recnum FROM cur1 INTO CURSOR cur2 NOFILTER
SELECT cur2.cod, cur2.name, cur2.deb, cur2.cre, SUM(cur22.balance) AS Sald FROM cur2 INNER JOIN cur2 AS cur22 ON cur2.name = cur22.name AND cur2.recnum >= cur22.recnum GROUP BY cur2.cod INTO CURSOR gridsource
If you didn't want to group on the "name" field you could take it out of the join condition. Also, I hope the name of the field isn't actually "name" since that is a reserved word.
It might be possible to simplify all this and eliminate some of the interim cursors. I don't think you can make an updateable view out of it, though.
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