Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Borislav Borissov
  Where is Borislav Borissov?
 Sofia
 Bulgaria
 Borislav Borissov
 To: Paul D'Anna
  Where is Paul D'Anna?
 Metairie
 Louisiana - United States
 Paul D'Anna
 Tags
Subject: RE: Use of 'SUM" command in Select field
Thread ID: 188923 Message ID: 188926 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, August 12, 2008 4:49:16 PM         
   


> I am looking for an easy way to "SUM" 120 fields in a SELECT statement.
>
> The cursor (Qa) that I wish to "SUM" looks like:
>
> Qa.NUMBER, Qa.WK001, Qa.WK002, Qa.WK003, ........ Qa.WK120
>
> I would like to sort and group this cursor by "NUMBER" into another cursor, but I don't want to repeat the "sum" 120 times.
>
> Example
> SUM(Qa.WK001) as "Wk001", SUM(Qa.WK002) as "Wk002", ...SUM(Qa.WK0120) as "Wk120"
>
> Is there some quick way to sort these fields since they all begin with "WK" and are sequenced with "001" through "120"
>
> Thanks in advance,
>
> Paul D'Anna


lcSQL = [SELECT Number]
FOR lnFor = 1 TO 120
    lcSQL = lcSQL + [, SUM(Qa.Wk]+PADL(lnFor, 3, [0])+[) AS Wk]+PADL(lnFor, 3, [0])+[)
NEXT
lcSQL = lcSQL + [ FROM Qa GROUP BY Number INTO CURSOR crsTest]
&lcSQL
BROWSE NORMAL

not tested
-----------------
Borislav Borissov

Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.

ENTIRE THREAD

Use of 'SUM" command in Select field Posted by Paul D'Anna @ 8/12/2008 4:42:50 PM
RE: Use of 'SUM" command in Select field Posted by Borislav Borissov @ 8/12/2008 4:49:16 PM
RE: Use of 'SUM" command in Select field Posted by Paul D'Anna @ 8/12/2008 5:57:33 PM
RE: Use of 'SUM" command in Select field Posted by Ilya Rabyy @ 8/12/2008 6:07:47 PM
RE: Use of 'SUM" command in Select field Posted by Ilya Rabyy @ 8/12/2008 5:01:28 PM
RE: Use of 'SUM" command in Select field Posted by Borislav Borissov @ 8/12/2008 5:19:56 PM
RE: Use of 'SUM" command in Select field Posted by Ilya Rabyy @ 8/12/2008 5:24:23 PM
RE: Use of 'SUM" command in Select field Posted by Cetin Basoz @ 8/12/2008 5:37:07 PM
RE: Use of 'SUM" command in Select field Posted by Borislav Borissov @ 8/12/2008 5:38:36 PM
RE: Use of 'SUM" command in Select field Posted by Ilya Rabyy @ 8/12/2008 6:04:46 PM
RE: Use of 'SUM" command in Select field Posted by Cetin Basoz @ 8/12/2008 5:16:18 PM
RE: Use of 'SUM" command in Select field Posted by Anders Altberg @ 8/12/2008 6:46:55 PM
RE: Use of 'SUM" command in Select field Posted by tushar @ 8/13/2008 5:47:02 AM