Evaluate 2 columns, add sum IF two columns satisfy the condition


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
# 1  
Question Evaluate 2 columns, add sum IF two columns satisfy the condition

HI All,

I'm embedding SQL query in Script which gives following output:

Code:
Assignee Group Total
ABC Group1 17
PQR Group2 5
PQR Group3 6
XYZ Group1 10
XYZ Group3 5

I have saved the above output in a file.
How do i sum up the contents of this output so as to get following output:

Again, how do i read the contents of the file so that I get the desired output?

Expected output:

Code:
Assignee Group1 Group2 Group3 Toatl_Assignee_Wise
ABC 17 0 0 17
PQR 0 5 6 11
XYZ 10 0 5 15
 
Grand Total: 43 (17+11+15)

Once i get this output, I'll be able to automate this script.

Can anyone please help me with this?

Moderator's Comments:
Mod Comment Use code tags, thanks.
# 2  
something along these lines - a bit verbose, but.....
awk -f kh.awk mySqlOutputFile where kh.awk is:
Code:
FNR > 1 {
 if (!($2 in gA)) {
   gA[$2]
   gAl[++gC]=$2
 }
 aA[$1,$2]+=$3
 aAa[$1]
}
END {
  printf("Assignee")
  for(i=1;i<=gC;i++)
    printf("%s%s%s", OFS, gAl[i], (i==gC)?OFS "Total_Assignee_Wise" ORS:"")

  for(j in aAa) {
    printf("%s", j)
    t=0
    for(i=1;i<=gC;i++) {
      idx=j SUBSEP gAl[i]
      t+=aA[idx]
      printf("%s%d%s", OFS,(idx in aA)?aA[idx]:0, (i==gC)?OFS t ORS:"")
    }
    tt+=t
  }
  printf("\nGrand Total: %d\n", tt)
}

This User Gave Thanks to vgersh99 For This Post:
# 3  
Hi vgersh99,

Thanks a ton for the solution!
But I would like to know the naming conventions you have used her such as gA, gAl, aAa, gC, aA since i would like to make some format changes in the code..
I would be greatful if you could please guide me line by line explanation for the same.. Smilie
Thanks again!
# 4  
Hi,
when i run this code i get the following error:

kh.awk: line 1: FNR: command not found
kh.awk: line 2: syntax error near unexpected token `{'
kh.awk: line 2: ` if (!($2 in gA)) {'

Can anyone please help?
# 5  
works fine for me.
what's in your kh.awkAND how do you invoke the code?
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Group by columns and add sum in new columns

Dear Experts, I have input file which is comma separated, has 4 columns like below, BRAND,COUNTRY,MODEL,COUNT NIKE,USA,DUMMY,5 NIKE,USA,ORIGINAL,10 PUMA,FRANCE,DUMMY,20 PUMA,FRANCE,ORIGINAL,15 ADIDAS,ITALY,DUMMY,50 ADIDAS,ITALY,ORIGINAL,50 SPIKE,CHINA,DUMMY,1O And expected output add... (2 Replies)
Discussion started by: ricky1991
2 Replies

2. Shell Programming and Scripting

Add new columns based on existing columns

Hi all, I am kind of stuck with printing my desired output. Please help me if you know how it can work. My input file(tab separated): NW_0068.1 41,16 100,900 NW_0699.1 4,2,19 200,700,80 My Output file (desired): NW_0068.1 41,16 100,900 100 - 141 NW_0068.1 41,16 100,900 ... (3 Replies)
Discussion started by: sam_2921
3 Replies

3. Shell Programming and Scripting

Read Two Columns - Apply Condition on Six other columns

Hello All, Here is my input univ1 chr1 100 200 - GeneA 500 1 0 0.1 0.2 0.3 0.4 0.5 univ1 chr1 100 200 - GeneA 600 1 0 0.0 0.0 0.0 0.0 0.1 univ1 chr1 100 200 - GeneA 700 1 0 0.4 0.4 ... (2 Replies)
Discussion started by: jacobs.smith
2 Replies

4. Shell Programming and Scripting

Add sum of columns and max as new row

Hi, I am a new bie i need some help with respect to shell onliner; I have data in following format Name FromDate UntilDate Active Changed Touched Test 28-03-2013 28-03-2013 1 0.6667 100 Test2 28-03-2013 03-04-2013 ... (1 Reply)
Discussion started by: gangaraju6
1 Replies

5. Shell Programming and Scripting

Get the SUM of TWO columns SEPARATELY by doing GROUP BY on other columns

My File looks like: "|" -> Field separator A|B|C|100|1000 D|E|F|1|2 G|H|I|0|7 D|E|F|1|2 A|B|C|10|10000 G|H|I|0|7 A|B|C|1|100 D|E|F|1|2 I need to do a SUM on Col. 5 and Col.6 by grouping on Col 1,2 & 3 My expected output is: A|B|C|111|11100 (2 Replies)
Discussion started by: machomaddy
2 Replies

6. Shell Programming and Scripting

Evaluate 2 columns, add sum IF two columns match on two rows

Hi all, I know this sounds suspiciously like a homework course; but, it is not. My goal is to take a file, and match my "ID" column to the "Date" column, if those conditions are true, add the total number of minutes worked and place it in this file, while not printing the original rows that I... (6 Replies)
Discussion started by: mtucker6784
6 Replies

7. Shell Programming and Scripting

If condition return 0 even when it fails to satisfy te condition

HI My doubt may be basic one but I need to get it clarified.. When i use "if" condition that checks for many AND, OR logical conditions like if ]; then return 0 fi Even the if condition fails it returns as zero.. Any clue.. But if i add else condition like if ]; ... (2 Replies)
Discussion started by: Priya Amaresh
2 Replies

8. Shell Programming and Scripting

Sum columns

Hi All, I'm new to this forum. So please be patience with me! :) I have a file that looks like this (all rows have the same number of columns): 19 20 30 15 17 38 51 60 74 85 96 07 .... 10 20 44 59 39 88 13 77 30 10 11 12 .... . . . I want to sum the value of first field to all the... (2 Replies)
Discussion started by: Aderson Nascime
2 Replies

9. Shell Programming and Scripting

Single command for add 2 columns and remove 2 columns in unix/performance tuning

Hi all, I have created a script which adding two columns and removing two columns for all files. Filename: Cust_information_1200_201010.txt Source Data: "1","Cust information","123","106001","street","1-203 high street" "1","Cust information","124","105001","street","1-203 high street" ... (0 Replies)
Discussion started by: onesuri
0 Replies

10. Shell Programming and Scripting

Sum of three columns - in 4N columns file

Hi All, happy new year. I have a file with 4xN columns like 0.0000e+00 0.0000e+00 7.199E+07 7.123E+07 6.976E+07 6.482E+07 5.256E+07 2.523E+07 0.0000e+00 0.0000e+00 8.641E+07 8.550E+07 8.373E+07 7.780E+07 6.309E+07 3.028E+07... (8 Replies)
Discussion started by: f_o_555
8 Replies

Featured Tech Videos