# Sum using awk

**URL:** <https://community.unix.com/t/sum-using-awk/265945>\
**Category:** Shell Programming and Scripting\
**Created:** [May 18, 2010, 9:20am UTC](https://community.unix.com/t/sum-using-awk/265945 "2010-05-18T09:20:47Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Katabatic](https://community.unix.com/letter_avatar/katabatic/32/6_22734026db12803ebb3e059622ac3534.png) [@Katabatic](https://community.unix.com/u/Katabatic)\
**Post date:** [May 18, 2010, 9:20am UTC](https://community.unix.com/t/sum-using-awk/265945/1 "2010-05-18T09:20:47Z")

</div>

Hi all,

I need to sum values for fields in a delimited file as below:

```nohighlight
 
2010-03-05|||
2010-03-05|||123
2010-03-05|467.621|369.532|
2010-03-06|||
2010-03-06||2|
2010-03-06|||444
2010-03-07|||
2010-03-07|||
2010-03-07|655.456|1019.301|

```

Code used is:

```nohighlight
 
nawk -F "|" ' { sum[$1] += $2; sum1[$1] +=$3; sum2[$1] +=$4 } END { for (k in sum) print k "|" sum[k] "|" sum1[k] "|" sum2[k] }'

```

output required (and achieved):

```nohighlight
 
2010-03-05|467.621|369.532|123
2010-03-06|0|2|444
2010-03-07|655.456|1019.301|

```

QUESTIONS:

1. When summing empty fields, how do I get the (sum) output value to reflect an empty field and not a zero?
2. This script needs to be used for multiple files with varying numbers of columns / fields. The above would require me to set up a seperate script for each input file and hardcode the number of columns (sections in blue) for every file. I would like to be able to write a single script to allow for any number of columns.

In searching for a solution, I understand that NF may work to do this but I have no idea of what the syntax should look like.

Can anyone assist?

Regards,

Bennie.

---

<div class="post-metadata">

**Author:** ![radoulov](https://community.unix.com/user_avatar/community.unix.com/radoulov/32/489_2.png) [@radoulov](https://community.unix.com/u/radoulov)\
**Post date:** [May 18, 2010, 9:50am UTC](https://community.unix.com/t/sum-using-awk/265945/2 "2010-05-18T09:50:48Z")

</div>

```nohighlight
nawk -F\| 'END {
  for (K in k) {
    printf "%s", K FS  
    for (i = 1; ++i <= nf;)
      printf "%s", (v[K, i] ? v[K, i] : x) \
       (i < nf ? FS : RS) 
    }
  }
{
  for (i = 0; ++i <= NF;) {
    v[$1, i] += $i; k[$1]
    }
  NF > nf && nf = NF    
  }' infile

```

---

<div class="post-metadata">

**Author:** ![curleb](https://community.unix.com/letter_avatar/curleb/32/6_22734026db12803ebb3e059622ac3534.png) [@curleb](https://community.unix.com/u/curleb)\
**Post date:** [May 18, 2010, 9:53am UTC](https://community.unix.com/t/sum-using-awk/265945/3 "2010-05-18T09:53:13Z")

</div>

At its base, you're going to need to establish some sort of pattern surrounding your requirements. Your sample appears to have sporadic values populated, but it also may be of mixed-precision. How many places do you want in these calculated values? Have you explored printf, as opposed to print? This allows you to apply masking symbols.

Insofar as the 0 sum columns, you could pipe the end result through a sed filter that eliminates the 0-string as needed.

You may wish to run it all through a case statement, or if..then loop, to apply different script versions according to your file's layout.

---

<div class="post-metadata">

**Author:** ![Tytalus](https://community.unix.com/user_avatar/community.unix.com/tytalus/32/87_2.png) [@Tytalus](https://community.unix.com/u/Tytalus)\
**Post date:** [May 18, 2010, 10:02am UTC](https://community.unix.com/t/sum-using-awk/265945/4 "2010-05-18T10:02:09Z")

</div>

something like:

```nohighlight
# nawk -F "|" '{k=NF;t[$1]++;for (i=2;i<=NF;i++){s[$1"_"i]+=$i}}END{for (i in t){str=i;for (x=2;x<=k;x++){str=str"|"s[i"_"x]};print str}}' infile 
2010-03-05|467.621|369.532|123
2010-03-06|0|2|444
2010-03-07|655.456|1019.3|0

```

should be close...

just saw Radoulovs - post - yep - you're still the master...

---

<div class="post-metadata">

**Author:** ![Katabatic](https://community.unix.com/letter_avatar/katabatic/32/6_22734026db12803ebb3e059622ac3534.png) [@Katabatic](https://community.unix.com/u/Katabatic)\
**Post date:** [May 18, 2010, 11:10am UTC](https://community.unix.com/t/sum-using-awk/265945/5 "2010-05-18T11:10:19Z")

</div>

Radoulov,

Just saw Tytalus's comment.

You are definitely the MASTER!

Works like a charm.

Thank you very much.

---

<div class="post-metadata">

**Author:** ![Katabatic](https://community.unix.com/letter_avatar/katabatic/32/6_22734026db12803ebb3e059622ac3534.png) [@Katabatic](https://community.unix.com/u/Katabatic)\
**Post date:** [May 19, 2010, 3:14pm UTC](https://community.unix.com/t/sum-using-awk/265945/6 "2010-05-19T15:14:47Z")

</div>

Hi Radoulov,

Apologies for bothering again. On checking the output again, I noticed that ALL the fields with ZERO or "BLANK" values are printed as "BLANK". In our environment however, ZERO has a specific meaning / value and therefore, if the summerized value is truly ZERO, I need it to display as such.

Or in other words, if 4 values are summed (3 Blanks and one ZERO), the output needs to reflect ZERO and not blank.

Hope you are able to assist.

Bennie.

---

<div class="post-metadata">

**Author:** ![radoulov](https://community.unix.com/user_avatar/community.unix.com/radoulov/32/489_2.png) [@radoulov](https://community.unix.com/u/radoulov)\
**Post date:** [May 19, 2010, 3:55pm UTC](https://community.unix.com/t/sum-using-awk/265945/7 "2010-05-19T15:55:37Z")

</div>

Try this one:

```nohighlight
nawk -F\| 'END {
  for (K in k) {
    printf "%s", K FS  
    for (i = 1; ++i <= nf;)
      printf "%s", ((K, i) in nn ? v[K, i] : x) \
        (i < nf ? FS : RS) 
    }
  }
{
  for (i = 1; ++i <= NF;) {
    v[$1, i] += $i
    $i == "" || nn[$1, i]
    }
  NF > nf && nf = NF; k[$1]  
    }' infile  

```

---

<div class="post-metadata">

**Author:** ![Katabatic](https://community.unix.com/letter_avatar/katabatic/32/6_22734026db12803ebb3e059622ac3534.png) [@Katabatic](https://community.unix.com/u/Katabatic)\
**Post date:** [May 19, 2010, 4:17pm UTC](https://community.unix.com/t/sum-using-awk/265945/8 "2010-05-19T16:17:39Z")

</div>

Radoulov,

You are truly a giant amongst men!

Thanks a lot.

Bennie.