Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Mismatch in summing a column in UNIX

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Mismatch in summing a column in UNIX
# 1  
Old 02-13-2015
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


Code:
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 more than 2 precisions)
-4994532.04
-2218492.52
-167183.69
103093000.43
123444.23



The issue is that,if I sum the column in excel sheet (with 4 precisions) it is coming to
-170552450514.8600 and in unix its
-170552450514.8603.

I am not sure what is causing the difference.Could you please help me with this
Last edited by karthik adiga; 02-13-2015 at 11:01 AM.. Reason: data values display
# 2  
Old 02-13-2015
Can you post all the values in code tags, you are trying to sum? Could be a rounding vs no rounding issue? If your values are rounded to 2 decimal places, I don't understand how can the sum go to 4 decimal places without padding 0s?
Last edited by senhia83; 02-13-2015 at 11:11 AM..
# 3  
Old 02-13-2015
there are 3703 rows..please find the attachment
unix .xls (53.6 KB) 
# 4  
Old 02-13-2015
Your file is not an XLS. Renaming it XLS has not made it actually be an XLS.

awk does not have infinite precision. If you need infinite precision, use the bc tool. If you can convince awk to print a list of lines like

Code:
a=-9992411.01
a=a+(-4992005.13)
...
a

and feed that into bc, it will print a total.

Code:
awk -F, 'NR==1 { print "a=",$7 ; next }  { print "a=a+("$7")"; } END { print "a" }' inputfile.csv | bc

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need help in comparing two files in UNIX with a mismatch

Hi Everyone, I am comparing results of two environments using unix files. I am writing two different csv file using spool and merging both the files using paste command paste -d, file1.csv file2.csv > prod_uat_result.csv and then finding the difference and attaching the same in a mail... (8 Replies)
Discussion started by: karthik adiga
8 Replies

2. Shell Programming and Scripting

awk split columns after matching on rows and summing the last column

input: chr1 1 2 3 chr1 1 2 4 chr1 2 4 5 chr2 3 6 9 chr2 3 6 10 Code: awk '{a+=$4}END{for (i in a) print i,a}' input Output: chr112 7 chr236 19 chr124 5 Desired output: chr1 1 2 7 chr2 3 6 19 chr1 2 4 5 (1 Reply)
Discussion started by: jacobs.smith
1 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

Count mismatch in UNIX

Hi, I have a requirement like below. client is sending the .txt filles.In that file we have 10 records but when I execute the below command it is showing 9 records. klena20> wc -l sample_file.txt|awk '{print $1}' It is showing the output as 9 But in a file records are 10. I found... (7 Replies)
Discussion started by: kirankumar
7 Replies

5. Shell Programming and Scripting

Please Help!!!! Awk for summing columns based on selected column value

a,b,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,bb,cc,dd,ee,ff,gg,hh,ii a thru ii are digits and strings.... The awk needed....if coloumn 9 == i (coloumn 9 is string ), output the sum of x's(coloumn 22 ) in all records and sum of y's (coloumn 23 ) in all records in a file (records.txt).... (6 Replies)
Discussion started by: BrownBob
6 Replies

6. Shell Programming and Scripting

Summing up rows data regarding 1st column

Dear all, I have one file like LABEL A B C D E F G H I J K L M N G02100 64651.3 25630.7 8225.21 51238 267324 268005 234001 52410.9 18598.2 10611 10754.7 122535 267170 36631.4 G02100 12030.3 8260.15 8569.91 ... (4 Replies)
Discussion started by: AAWT
4 Replies

7. 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

8. Shell Programming and Scripting

summing values of a column

I have a file which contains data as below: ----------------------------------------------------------------------------------------------- GSPWeb Statistics for the period of last 20 days... (3 Replies)
Discussion started by: mohsin.quazi
3 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
Login or Register to Ask a Question