Sum in file based column

UNIX for Beginners Q & A
1

Hi All,

I have a file as below and want to sum based on the id in the first column

Input
10264;ATE; 12
10265;SES;11
10266AUT;50
10264;ATE;10
10265;SES;13
10266AUT;89
10264;ATE;1
10265;SES;15
10266AUT;78

Output
10264;ATE; 23
10265;SES;39
10266AUT;139

I tried below 2 and not getting the output

awk '{split ($1,a,";");sum[a[1]] += $NF;} END { for (s in sum) print s,sum;} myfile -- Not working
awk -F'[;]' '{ t[$1]+=$3 } END { for (i in t) print i, t }' -- myfile -- Not working

Thanks

2

I tried the second one and it works, but your input file seems to broken. Try it with this one:

10264;ATE;12
10265;SES;11
10266;AUT;50
10264;ATE;10
10265;SES;13
10266;AUT;89
10264;ATE;1
10265;SES;15
10266;AUT;78

Or change your code to deal with a different file format..

1 Like
3

Your data appears to have variable number of columns. The code you wrote will work if all rows in your input file has exactly three columns.

But if that is how your input data is, then one approach is to create an associative array with index/key by removing the last column:-

awk -F\; '
        {
                idx = $0
                sub(/[;][^;]*$/,X,idx)
                A[idx] += $NF
        }
        END {
                for ( k in A )
                        print k, A[k]
        }
' OFS=\; file
1 Like
4

Thanks . When I tried to print the description using the below command I am getting only one description not sure what I am doing wrong

awk -F'[;]' '{ t[$1]+=$3 } END { for (i in t) print i,$2, t }'  my file

INPUT
10264;ATE;12
10265;SES;11
10266;AUT;50
10264;ATE;10
10265;SES;13
10266;AUT;89
10264;ATE;1
10265;SES;15
10266;AUT;78


OUTPUT
10266 AUT 217
10264 AUT 23
10265 AUT 39

Expected
10266 AUT 217
10264 ATE 23
10265 SES 39

Also if I have the number as 1009090992.1111 the my result is coming as 1.00909e+09 how to get that as a whole number

5 
awk -F'[;]' '{ t[$1 OFS $2]+=$3 } END { for (i in t) print i, t }'  myFile

For the 'Also' part... please post sample representative input.

1 Like
6

Below is the example if also part

Example: 
10264;ATE;1009090992.1111
10265;SES;11
10266;AUT;50
10264;ATE;10
10265;SES;13
10266;AUT;89
10264;ATE;1
10265;SES;15
10266;AUT;78

Output
10264 ATE 1.00909e+09 -->  I am trying with printf to get it work
10266 AUT 217
10265 SES 39
7

Use "%.f" as the format string.