How to sum multiple column output with awk ?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to sum multiple column output with awk ?
# 1  
Old 12-22-2012
How to sum multiple column output with awk ?

Hi Experts, I am trying to sum multiple columns and rows with awk ,

I want the sum of :
1] Horizontal Sum: (rows sum):
2] Vertical Sum: (Column's sum]


details:

Code:
# cat file1
10 11 12 13 14 15 16 17 18 19
20 21 22 23 24 25 26 27 28 29
40 31 32 33 34 35 36 37 38 39
70 41 42 43 44 45 46 47 48 49
50 51 52 53 54 55 56 57 58 59
60 61 62 63 64 65 66 67 68 69
70 71 72 73 74 75 76 77 78 79
20 81 82 83 84 85 86 87 88 89
05 91 92 93 94 95 96 97 98 99



I am trying with this :

Horizontal sum:
Code:
# awk '{X=$0}{split(X,x)}{print X , " =" x[1]+x[2]+x[3]+x[4]+x[5]+x[6]+x[7]+x[8]+x[9]+x[10] }' file1| tr " " "\t"
10      11      12      13      14      15      16      17      18      19              =145
20      21      22      23      24      25      26      27      28      29              =245
40      31      32      33      34      35      36      37      38      39              =355
70      41      42      43      44      45      46      47      48      49              =475
50      51      52      53      54      55      56      57      58      59              =545
60      61      62      63      64      65      66      67      68      69              =645
70      71      72      73      74      75      76      77      78      79              =745
20      81      82      83      84      85      86      87      88      89              =785
05      91      92      93      94      95      96      97      98      99              =860



Vertical Sum:
Code:
 $ awk '{s1+=$1;s2+=$2;s3+=$3;s4+=$4;s5+=$5;s6+=$6;s7+=$7;s8+=$8;s9+=$9;s10+=$10}END{print s1,s2,s3,s4,s5,s6,s7,s8,s9,s10}' file1 | tr " " "\t" 
345     459     468     477     486     495     504     513     522     531



I want the output to be look like this:
Code:
10      11      12      13      14      15      16      17      18      19              =145
20      21      22      23      24      25      26      27      28      29              =245
40      31      32      33      34      35      36      37      38      39              =355
70      41      42      43      44      45      46      47      48      49              =475
50      51      52      53      54      55      56      57      58      59              =545
60      61      62      63      64      65      66      67      68      69              =645
70      71      72      73      74      75      76      77      78      79              =745
20      81      82      83      84      85      86      87      88      89              =785
05      91      92      93      94      95      96      97      98      99              =860
345     459     468     477     486     495     504     513     522     531




How to make it easy and into one command, with loops and not by typing 1 to 10 iterations.


Thanks,..

Last edited by rveri; 12-22-2012 at 06:51 AM..
# 2  
Old 12-22-2012
You can just sum it up as you go through each line:
Code:
$ cat myScript
awk '{ 
  TR=0
  for( I = 1; I <= NF; I++ ) {
    TR += $I
    TC[I] += $I
    printf( "%6d", $I )
  }
  print "  = " TR
  TF = NF
}

END {
  for( I = 1; I <= TF; I++ )
    printf "%6d", TC[I]
  print ""
}
' file

$ cat file
10 11 12 13 14 15 16 17 18 19
20 21 22 23 24 25 26 27 28 29
40 31 32 33 34 35 36 37 38 39
70 41 42 43 44 45 46 47 48 49
50 51 52 53 54 55 56 57 58 59
60 61 62 63 64 65 66 67 68 69
70 71 72 73 74 75 76 77 78 79
20 81 82 83 84 85 86 87 88 89
05 91 92 93 94 95 96 97 98 99

$ ./myScript
    10    11    12    13    14    15    16    17    18    19  = 145
    20    21    22    23    24    25    26    27    28    29  = 245
    40    31    32    33    34    35    36    37    38    39  = 355
    70    41    42    43    44    45    46    47    48    49  = 475
    50    51    52    53    54    55    56    57    58    59  = 545
    60    61    62    63    64    65    66    67    68    69  = 645
    70    71    72    73    74    75    76    77    78    79  = 745
    20    81    82    83    84    85    86    87    88    89  = 785
     5    91    92    93    94    95    96    97    98    99  = 860
   345   459   468   477   486   495   504   513   522   531

This User Gave Thanks to Scott For This Post:
# 3  
Old 12-24-2012
Thanks Scott,
This works like charm and great coding, still trying to understand it.
Thanks a lot.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Do replace operation and awk to sum multiple columns if another column has duplicate values

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For... (12 Replies)
Discussion started by: as7951
12 Replies

2. Shell Programming and Scripting

awk to Sum columns when other column has duplicates and append one column value to another with Care

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For... (1 Reply)
Discussion started by: as7951
1 Replies

3. Shell Programming and Scripting

Need to sum up a column value from multiple files into Credit and Debit categories using awk command

i have multiple files with Batch Header, Record detail & Batch trailer data in the files like : BH 20150225950050N8262 RD 20140918000000 99999999 unk Deferred Settlement -13950 BT01 -13950 *Above sample data donot have the spaces coorectly defined. I do have multiple batch trailer... (1 Reply)
Discussion started by: kcdg859
1 Replies

4. Shell Programming and Scripting

Sum values of specific column in multiple files, considering ranges defined in another file

I have a file (let say file B) like this: File B: A1 3 5 A1 7 9 A2 2 5 A3 1 3 The first column defines a filename and the other two define a range in that specific file. In the same directory, I have also three more files (File A1, A2 and A3). Here is 10 sample lines... (3 Replies)
Discussion started by: Bastami
3 Replies

5. Shell Programming and Scripting

awk to sum a column based on duplicate strings in another column and show split totals

Hi, I have a similar input format- A_1 2 B_0 4 A_1 1 B_2 5 A_4 1 and looking to print in this output format with headers. can you suggest in awk?awk because i am doing some pattern matching from parent file to print column 1 of my input using awk already.Thanks! letter number_of_letters... (5 Replies)
Discussion started by: prashob123
5 Replies

6. Shell Programming and Scripting

Sum of a column in multiple files

I am performing the following operation on a file that looks like this 1000 0 10 479.0 1115478.07497 0.0 0.0 0.0872665 1000 10 20 1500.0 3470012.29304 0.0 0.0 0.261799 1000 20 30 2442.0 5676346.87758 0.0 0.0 0.436332 1000 30 40 3378.0 7737905.30957 0.0 0.0 0.610865 1000 40 50 4131.0... (2 Replies)
Discussion started by: kayak
2 Replies

7. Shell Programming and Scripting

Sum a column value based on multiple keys

Hi, I have below as i/p file: 5ABC 36488989 K 000010000ASB BYTRES 5PQR 45757754 K 000200005KPC HGTRET 5ABC 36488989 K 000045000ASB HGTRET 5GTH 36488989 K 000200200ASB BYTRES 5FTU ... (2 Replies)
Discussion started by: nirnkv
2 Replies

8. Shell Programming and Scripting

sum multiple columns based on column value

i have a file - it will be in sorted order on column 1 abc 0 1 abc 2 3 abc 3 5 def 1 7 def 0 1 -------- i'd like (awk maybe?) to get the results (any ideas)??? abc 5 9 def 1 8 (2 Replies)
Discussion started by: jjoe
2 Replies

9. Shell Programming and Scripting

scripting/awk help : awk sum output is not comming in regular format. Pls advise.

Hi Experts, I am adding a column of numbers with awk , however not getting correct output: # awk '{sum+=$1} END {print sum}' datafile 2.15291e+06 How can I getthe output like : 2152910 Thank you.. # awk '{sum+=$1} END {print sum}' datafile 2.15079e+06 (3 Replies)
Discussion started by: rveri
3 Replies

10. UNIX for Dummies Questions & Answers

Column containing sum using awk

Hi All, I am trying to add a column that contains the sum of the previous column repeated. IE 1 2 3 4 I would like to get: 1 10 2 10 3 10 4 10 Advice? I can get 1 1 2 3 3 6 (4 Replies)
Discussion started by: baconbasher
4 Replies
Login or Register to Ask a Question