Visit Our UNIX and Linux User Community


summing values of a column


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting summing values of a column
# 1  
Old 08-24-2009
summing values of a column

I have a file which contains data as below:

Code:
-----------------------------------------------------------------------------------------------
                      GSPWeb Statistics for the period of last 20 days
-----------------------------------------------------------------------------------------------
  NBKID                 PAGE ACCESSED                                   PAGE COUNT
-----------------------------------------------------------------------------------------------
nbk1j7o              RMBS -> RMBSHome                                           1
nbk1j7o              RMBS -> TradingStrategy                                    1
nbk2coz              GSF -> GSFHome                                             1
nbk2coz              RMBS -> AgencyCMO                                          1
nbk2coz              RMBS -> PassThrough                                      102
nbk2coz              RMBS -> Prepayment                                       135
nbk2coz              RMBS -> RMBSHome                                           5
nbk2coz              RMBS -> TradingStrategy                                    6
nbk2gdl              GSF -> GSFHome                                           293
nbk2mcs              RMBS -> TradingStrategy                                    1
nbk2mfb              RMBS -> RMBSHome                                          41
nbk2w2w              GSF -> GSFHome                                             1

I want to find the sum of values in third column grouping by RMBS, GSF.

For example output should contain:

Code:
 
Total RMBS = 293
Total GSF = 295

This is part of a big project with lots of efforts I have reached this stage, was little stuck, awk command should help, but dont know how. Smilie
# 2  
Old 08-24-2009
Try...
Code:
 
awk 'NR>5{arr[$2]=arr[$2]+$5}END{for(i in arr) print "Total " i,arr[i]}' infile

# 3  
Old 08-24-2009
Thanks a lot !

Thanks buddy, this works I did a little more modifications to remove unnecessary content.
# 4  
Old 08-25-2009
perl:

Code:
open FH,"<a";
while(<FH>){
 if(/\S*\s*(\S+)\s*->.*\s([0-9]+)/){
   $hash{$1}+=$2;
 }
}
foreach my $key ( keys %hash){
  print $key,"->",$hash{$key},"\n";
}


Previous Thread | Next Thread
Test Your Knowledge in Computers #52
Difficulty: Easy
A magnetic disc array is an example of offline storage.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Mismatch in summing a column in UNIX

Hello, I am facing issue in summing up a column in unix.I am displaying a column sum up to 4 decimal places and below is the code snippet sed '1d' abc.csv | cut -d',' -f7 | awk '{s+=$1}END{ printf("%.4f\n",s)}' -170552450514.8603 example of data values in the column(not... (3 Replies)
Discussion started by: karthik adiga
3 Replies

2. Shell Programming and Scripting

Summing up values of rows of numbers

data file contains failed=24 error=23 error=163 failed=36 error=903 i need to get a total count of each value above. i'm looking for the most efficient method to do this as the datafile i provided is just a sample. the actual data can be several hundred thousands of lines. so from... (3 Replies)
Discussion started by: SkySmart
3 Replies

3. Shell Programming and Scripting

Summing a number column

hi All, i have a file in which only one column is there., test.txt ====== -900.01 -900.02 -900.03 -900.04 -900.05 -900.06 -900.07 -900.08 -900.09 900.01 900.02 900.03 900.04 900.05 (4 Replies)
Discussion started by: mechvijays
4 Replies

4. Shell Programming and Scripting

reading a script and summing some values

I need help reading and summing some values in a file that looks like the following. This is an Oracle trace file. Oracle has as utility to do this,but it doesn't work properly unless my sql statement is done. I want to read the file and sum up some values to let me know how the query/job is... (0 Replies)
Discussion started by: guessingo
0 Replies

5. Shell Programming and Scripting

Summing values in columns

Basically I have to process a text file which has been sorted this way: John 12 John 13 John 10 John 900 Peter 20 Peter 30 Peter 32 The first column is a name, and the second an arbitrary value, both delimited by a space. How can I sum them up such that it would become: John 935... (2 Replies)
Discussion started by: Dwee
2 Replies

6. Shell Programming and Scripting

Summing column value - using PERL

I'm new to perl programming. I've a csv file as below. 20100221, abc_1, 200 20100221, abc_4, 350 20100221, opq_3, 200 20100221, abc_5, 220 20100221, xyz_1, 500 20100221, abc_2, 500 20100221, abc_3, 100 20100221, xyz_2, 700 20100221, opq_2, 350 20100221, xyz_3, 100 20100221, opq_1, 230... (8 Replies)
Discussion started by: ganapati
8 Replies

7. Shell Programming and Scripting

Awk: Summing values with group criteria

Hi Guys, I have a text file with ";" like separator F1;F2;F3;F4;F5 444;100041;IT;GLOB;1800000000 444;100041;TM;GLOB;1000000000 444;10300264;IT;GLOB;2000000000 444;10300264;IT;GLOB;2500000000 I have to sum the cullums F5 for same F2 and F3 collums The result must be: ... (7 Replies)
Discussion started by: gianluca2
7 Replies

8. Shell Programming and Scripting

summing up iostat values in AIX

Friends, I have to run iostat -d on my AIX machine and print the sum of the output in tps column per iteration. can any one pls guide me how to do this using awk. here is the sample output iostat -d 2 2 | awk '!/System/ && !/Disks/ && !/cd/ && !/^$/ {print $4}' 2.0 3.0 1.0 3.0... (1 Reply)
Discussion started by: achak01
1 Replies

9. UNIX for Dummies Questions & Answers

summing according to the column

I have a text file with two columns the first column is an integer and the second column is date how do i sum up the first column according to the date example 123 jan1 232 jan1 473 jan2 467 jan2 356 jan3 376 jan3 my result should be 355 jan1 940 jan2 732 jan3 how do i... (2 Replies)
Discussion started by: ramky79
2 Replies

10. Shell Programming and Scripting

Summing on column

Hi Friends How to do sum on a column? I have a file like: FRED 500.01 TX SMITH 50.10 NY HARRY 5.00 CA 555.11 Sum on second column. I am trying using nawk like nawk 'BEGIN {FS="|"}; {printf $1"+"}' Thanks a lot for your help S :) (2 Replies)
Discussion started by: sbasetty
2 Replies

Featured Tech Videos