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

HI All,

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

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:

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?

something along these lines - a bit verbose, but.....
awk -f kh.awk mySqlOutputFile where kh.awk is:

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==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
      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)
}
1 Like

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.. :slight_smile:
Thanks again!

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?

works fine for me.
what's in your kh.awk AND how do you invoke the code?