Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 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: 188942 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Databases, Tables and SQL Server
Date: Tuesday, August 12, 2008 6:46:55 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

The quickest way to get the sum of all numeric fields in a table is our long-forgotten friend the SUM command:

Select MyTable 
SUM TO ARRAY aSums 
* If all you have in that table are the numeric columns the rest is simple
INSERT INTO MyTable FROM ARRAY aSums
* If there are various non-numeric columns among them: 
SELECT * FROM MyTable WHERE 1=0 INTO CURSOR Q1 READWRITE
ALTER TABLE Q1 DROP col1 DROP col24 DROP col100
INSERT INTO Q1 FROM ARRAY aSums
SELECT MyTable 
APPEND FROM DBF('Q1') 
* The fields will be mapped right, by name
* There's also 
SELECT MyTable 
APPEND BLANK 
GATHER FROM aSums FIELDS LIKE WK* 
* or 
GATHER FROM aSums FIELDS EXCEPT col1, col24, col100

Have fun, but how immensely much simpler your life would be sometimes if you worked with normalized tables, don't you think?
-Anders

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