Substitute to GROUP BY function

Shell Programming and Scripting
1

Hi All,
I really need a help on this thing. Most of us are aware about the group by function in Oracle. Do we have a substitute ( not necessarily a single line command) to it in Unix?
Let me put it this way.
I have a file whose content is like
file1-:

ID1,ID2,ID3,ID4,ID5
1,2,3,123,5
1,5,4,154,2
1,2,3,123,4
2,3,5,235,3
1,5,4,154,1
2,3,5,235,2
3,2,5,325,5
2,3,5,235,6

The first line is not the content of the file. Rather I put that to identify the the fields. ID3 is nothing but "ID1||ID2||ID3".
Now we need to sort the file1 based on ID4 and then sum up the ID4 values for the same ID3. This sum-ed up value will be ID5. And ID6 will be the number of records involved in the group.
My final output should be like
1,2,3,123,9,2
1,5,4,154,3,2
2,3,5,235,11,3
3,2,5,325,5,1
Is not this what the GROUP BY function performs in Oracle? If my words are confusing then sorry for that. But the i/p and o/p files are self explanatory. Ain't they?
Thanks in advance for any help.

Many regards,
Rinku..

2

Here's a quick nawk solution; I'm sure that you will want to muck with it some (just demonstrating that it can be done without too much difficulty):

nawk -F\, '{
    keys[$4] += 1
    sums[$4] += $5
    other_stuff[$4] = $1 "," $2 "," $3
}
END {
    for (j in keys) 
        print other_stuff[j] "," i "," sums[j] "," keys[j]
}' yourdatafile | sort -t\, -k4

1,2,3,123,9,2
1,5,4,154,3,2
2,3,5,235,11,3
3,2,5,325,5,1
3

Thanks tmaricle,
Your solution really works. I was new to the Associative array concept in awk. This different version of for loop really hold a good power to deal with such sort of problems. I am sure this solution must give a good deal of information to the entire forum.
Thanking you once again...

Kind regards,
Rink...

4

:smiley: I wish I could take more credit; these are almost my very words a year ago when vgersh99 enlightened me in this grouping technique (see this post).