sum of 2nd field

[Rinky]

Hello experts,
I have a big file with numbers in columns. I want to take the sum of the 2nd field in the file.
I used:
cat file|awk '{s=s+$2; print s}'.
But it is giving a exponential value like 6.56616e+09. How can I get the sum in number format?

[Steven E. Protter]

Shalom,

printf

http://www.gnu.org/manual/gawk/html_node/Format-Modifiers.html

http://www.uga.edu/~ucns/wsg/unix/awk/

http://www.unix.com/shell-programming-scripting/77874-awk-printf-formatting-using-string-format-specifier-2.html

SEP

[Rinky]

It is not working. I used awk '{s=s+$2; printf("%d\n",s)}'
But it somehow is adding upto 20,000 lines and then subracting the later values I am guessing.
I got 2147483647 from the above command, but 2188720747 when I add in excel.
I am attaching the file for use.

[Michael Mike Reaser]

cat file | awk 'BEGIN{s=0}{s=s+$2}END{printf("%15d\n",s)}'

Or, shorter

awk 'BEGIN{s=0}{s=s+$2}END{printf("%15d\n",s)}' < file

[Michael Mike Reaser]

The maximum value that can be stored in a 32-bit signed integer is 2147483648. Your sum is greater than that, so yes, you're "overflowing" into negative values.

[James R. Ferguson]

Hi Rinky:

Do some thing like:

# awk 'END{printf("%.0f\n",s)};s+=$2' file

Notice the use of the float specification of 'printf'. Notice too the elimination of the extra 'cat' process.

I also short-handed notation for the auto summation to your 's' variable.

Regards!

...JRF...

[Peter Nikitka]

Hi,

all small adding to JRF's solutions:

If you do not want to get an echo of each input line (default action in awk), put the summation into an action block instead of the pattern block:

awk 'END{printf("%.0f\n",s)} {s+=$2}' file

mfG Peter

[James R. Ferguson]

Hi (again):

By the way, if you don't want to see the individual records and sums as you process them, you could do:

# awk 'END{printf("%.0f\n",s)};NF>0 {s+=$2}' file

Regards!

...JRF...

[Dennis Handly]

>Mike: The maximum value that can be stored in a 32-bit signed integer is 2147483648.

There's no stinkin' integers here. awk uses doubles.
http://forums.itrc.hp.com/service/forums/questionanswer.do?threadId=1267952

>you're "overflowing" into negative values.

You're switching from fixed point output to exponential format because it is too wide for %g. See awk(1):
CONVFMT: Internal conversion format for numbers (default %.6g). If the value of CONVFMT is not a floating-point format specification, the results are unspecified.

>JRF: awk 'END{printf("%.0f\n",s)};NF>0 {s+=$2}' file

If you are going to check NF, why not make sure $2 exists? NF >= 2

Also, I typically put END last as a visual clue.

[James R. Ferguson]

Hi (again):

> Dennis: @ JRF: If you are going to check NF, why not make sure $2 exists? NF >= 2

...because 'awk' would treat absent fields as zero :-;

That aside, Peter's post is a more concise representation of my objective, although I posted before I saw his.

> Dennis: Also, I typically put END last as a visual clue.

Either that way, or at the beginning to high-light the objective of the script as in this case. :-;

Regards!

...JRF...

[Suraj K Sankari]

Hi,

As Ferguson give the right solution I am adding one small thing.
If you are new to printf or you wana add decimal should come with total then do this

Ferguson :
# awk 'END{printf("%.0f\n",s)};NF>0 {s+=$2}' file

Just replace 0 with how many decimal you want
# awk 'END{printf("%.2f\n",s)};NF>0 {s+=$2}' file

Suraj