I have a file with the following content (there are additional spaces to pad the first column)

  1 account1  
  1 account1   
 19 account2   
100 account3   
  1 account3

And I am trying to get to an output of

  2 account1,  
 19 account2  
101 account3,   

which I will loop through so that I can create an html table to email to an account. I managed to get the list of IPs per account using :

awk '{a[$2]=a[$2]" " $3}END{for (i in a) print i" " a[i]}' inputfile

but I cannot get to sum the first column as well.


Adding the sum to your script shouldn't be too hard, just add another array to hold it.

$ awk '{ if (a[$2]) a[$2] = a[$2] ", ";
         a[$2] = a[$2] $3;
         sum[$2] += $1 }
       END {for (x in a) printf "%3d %s %s\n", sum[x], x, a[x]}' inputfile
  2 account1,
 19 account2
101 account3, 

(Your sample output had the IP address separated by commas, so I added that too. Though at least the code is neater without it, in my opinion.)


Awk solution:

awk 'NR==1{ match($0, /^ +[^ ]+/); s=length(substr($0, RSTART, RLENGTH)) }
     { sum[$2]+=$1; ips[$2]=($2 in ips? ips[$2]", ":"")$3 }
         for (i in sum) 
             printf("%*s %s %s\n", s, sum[i], i, ips[i]) 
     }' file

The output:

  2 account1,
 19 account2
101 account3,

I'm probably overthinking this, (there's likely a nice awk/sed to be used) but this is the best thing I can come up with:

# set file as sys argument

# pull unique account names into an array
account_names=($(awk '{print $2}' $file | sort | uniq))

# loop through and store column values
for account in ${account_names[@]}; do
    # get a sum of the first column
    col1=$(grep $account $file | awk '{SUM += $1} END {print SUM}')
    # get last column and convert to one line with comma separation
    IP_list=$(grep $account $file | awk '{print $NF}' | sort | uniq | tr '\n' ',' | sed s'/.$//')
    # print them together
    echo "$col1 $account $IP_list"

Then you can run it like so:

┌─[robotjohny@Fedora]─[~]─[04:08 pm]
└─[$]› ./test1.sh file1.txt 
2 account1,
19 account2
101 account3,
  • 1
    this makes for a very good reusable function, thank you – C.Mallia Jan 24 '18 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.