How can we sum the values group by from file using shell script

Question

I have a file where I have student Roll no, Name, Subject, Obtain Marks and Total Marks data:
10 William English 80 100
10 William Math 50 100
10 William IT 60 100
11 John English 90 100
11 John Math 75 100
11 John IT 85 100

How can i get Group by sum (total obtained marks) of every student in shell Shell? I want this output:
William 190
John 250

i have tried this:
cat student.txt | awk '{sum += $14}END{print sum" "$1}' | sort | uniq -c | sort -nr | head -n 10

This is not working link group by sum.

thanasisp · Accepted Answer

With one awk command:
awk '{a[$2]+=$4} END {for (i in a) print i,a[i]}' file

Output
William 190
John 250

If you want to sort the output, you can pipe to sort, e.g. descending by numerical second field:
awk '{a[$2]+=$4} END {for (i in a) print i,a[i]}' file | sort -rnk2

or ascending by student name:
awk '{a[$2]+=$4} END {for (i in a) print i,a[i]}' file | sort

Digvijay S · Answer

You need to use associative array in awk.
Try
awk '{ a[$2]=a[$2]+$4 } END {for (i in a) print i, a[i]}'

a[$2]=a[$2]+$4  Create associate array with $2 as index and sum of values $4 as value
END <-- Process all records
for (i in a) print i, a[i] <-- Print index and value of array
Demo :
$awk '{ a[$2]=a[$2]+$4 } END {for (i in a) print i, a[i]}' temp.txt 
William 190
John 250
$cat temp.txt 
10 William English 80 100
10 William Math 50 100
10 William IT 60 100
11 John English 90 100
11 John Math 75 100
11 John IT 85 100
$

How can we sum the values group by from file using shell script

2 Answers

Add your own answers!

Ask a Question