Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Awk-using group function

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Awk-using group function
# 1  
Old 08-21-2012
Awk-using group function
Hi,
I have file with below format and sample data - File is pipe delimited
Code:
Col1|col2|Account|Bal1|Bal2
1|2|1|10|5
1|2|2|10|2
1|3|3|10|3

I want output as
Code:
SUM|1|2|2|20|7
SUM|1|3|1|10|3

Can anyone give me awk command
Last edited by Franklin52; 08-21-2012 at 08:29 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 08-21-2012
how you got the required output
# 3  
Old 08-21-2012
This is Output
Code:
SUM|1|2|2|20|7
SUM|1|3|1|10|3


First col is fized string
Second and thrid column are col1 and col 2 from input, fouth column is recound count bases on col and col2, fifth column and six are summation on input 4 and 5 col.


Moderator's Comments:
Mod Comment Please use code tags next time for your code and data.
Last edited by zaxxon; 08-21-2012 at 09:07 AM.. Reason: code tags
# 4  
Old 08-21-2012
Try...
Code:
$ cat file1
Col1|col2|Account|Bal1|Bal2
1|2|1|10|5
1|2|2|10|2
1|3|3|10|3

$ awk 'BEGIN{FS=OFS="|"}
    NR>1{
      i = $1 FS $2
      a[i]++
      b[i]+=$(NF-1)
      c[i]+=$NF
    }
    END{
      for(i in a)
        print "SUM", i, a[i], b[i], c[i] | "sort"
    }' file1 > file2

$ cat file2
SUM|1|2|2|20|7
SUM|1|3|1|10|3

$

# 5  
Old 08-21-2012
Code:
awk -F"|" '{a[$1FS$2]++;b[$1FS$2]+=$4;c[$1FS$2]+=$5}END{for (i in a) print "SUM|"i,a[i],b[i],c[i]}' OFS="|" infile

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Group and search using awk

file: Data has to be grouped on 1st field; And in each group, print 2nd field IF 1st field doesn't match with any of the values in 2nd field.. o/p to be: I ended up writing as below, any help ?? awk -F, '!($1 in a){a++;}END{for (i in a)print i}' file (3 Replies)
Discussion started by: JSKOBS
3 Replies

2. Shell Programming and Scripting

Need help on awk for printing the function name inside each function

Hi, I am having script which contains many functions. Need to print each function name at the starting of the function. Like below, functionname() { echo "functionname" commands.... } I've tried like below, func=`grep "()" scriptname | cut -d "(" -f1` for i in $func do nawk -v... (4 Replies)
Discussion started by: Sumanthsv
4 Replies

3. Programming

Sql ORA-00937: not a single-group group function

I'm trying to return only one row with the highest value for PCT_MAX_USED. Any suggestions? When I add this code, I get the ORA-00937 error. trunc(max(decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100))) pct_max_used This is the original and returns all rows. select (select... (3 Replies)
Discussion started by: progkcp
3 Replies

4. Shell Programming and Scripting

awk - how do i get the last row of a group

How do i print the last record of a group in a file ? For example, I have a file like this : cat txt cucm1,location1,1,2,3 cucm2,location1,3,4,5 cucm1,location1,10,20,30 cucm2,location2,30,40,50 I am expecting a command that would print the last record of the group. For example, ... (2 Replies)
Discussion started by: Lakshmikumari
2 Replies

5. Shell Programming and Scripting

Group by using awk

Hi All, I have a file in the following format... ***************************************************** 11/10/27 12:09 : Input Record 11/10/27 12:10 : Input Record 11/10/27 12:10 : Input Record 11/10/27 12:10 : Input Record 11/10/27 12:10 : Input Record 11/10/27 12:10 : Input... (2 Replies)
Discussion started by: sraj142
2 Replies

6. Shell Programming and Scripting

Awk problem: How to express the single quote(') by using awk print function

Actually I got a list of file end with *.txt I want to use the same command apply to all the *.txt Thus I try to find out the fastest way to write those same command in a script and then want to let them run automatics. For example: I got the file below: file1.txt file2.txt file3.txt... (4 Replies)
Discussion started by: patrick87
4 Replies

7. Shell Programming and Scripting

awk group by

Hi I have a file in this format: ... 04/May/2009 16 04/May/2009 1 05/May/2009 3 05/May/2009 5 06/May/2009 1 06/May/2009 3 ... I need to sum for every day, What is the best way? Thanks all (2 Replies)
Discussion started by: mutti
2 Replies

8. Shell Programming and Scripting

Awk-Group count of field

Hi, Suppose if i am having a file with following records as given below. 5555 6756 5555 4555 4555 6767 how can i get the count of each record using AWK. Eg:5555 count should be 2 4555 count should be 2 6767 count should be 1 ... (5 Replies)
Discussion started by: tinivt
5 Replies

9. Shell Programming and Scripting

awk help required to group output and print a part of group line and original line

Hi, Need awk help to group and print lines to format the output as shown below INPUT FORMAT set echo on set heading on set spool on /* SCHEMA1 */ CREATE TABLE T1; /* SCHEMA1 */ CREATE TABLE T2; /* SCHEMA1 */ CREATE TABLE T3; /* SCHEMA1 */ CREATE TABLE T4; /* SCHEMA1 */ CREATE TABLE T5;... (5 Replies)
Discussion started by: rajan_san
5 Replies

10. Shell Programming and Scripting

Substitute to GROUP BY function

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... (3 Replies)
Discussion started by: rinku11
3 Replies
Login or Register to Ask a Question