Sum Dollar Amounts

Shell Programming and Scripting
1

I get a transaction file and I need to sum two of the columns. I each record I get a debit(D) or credit(C) indicator. Then I get an amount field. I need to sum the total dollar value of debits and credits.

I know I can loop through each record and get the sum total but is there a better way with awk or something?

Any help would be appreciated.

Thanks.

2

Do you have a sample of the input file containing the values.

Look at this sample file and script

$ cat sum.txt
12 23
23 34
34 45
45 56
$ awk '{ print $1+$2 }' sum.txt  
35
57
79
101
3

I don't have the sample file yet but it would be something like

Header Record
Indicator 'HR'
Date 'YYYYMMDD'
Version 1

Record Layout
acct no varchar2(10)
indicator varchar2(1)
amt number

Trailer
Indicator 'TR'
Total Rec cnt not includind header or trailer
Total Credits sume of the amount of credits
Total Debits sum of the amt of debits

Sample file

HR|20050909|1
1111111111|C|100.00
2222222222|D|200.00
3333333333|C|50.00
4444444444|D|10.00
TR|4|150.00|210.00

So the program should be able to come up with Total Credits = 150.00 and Total Debits = 110.00. The file I will be working with will have around 100,000 records. I want to compare my count to the trailer to verify the file contents.

4

Hi
Just Try this

echo "Total Credit = " $( grep "\|C\|" YourFile| awk -F"|" '{ S = S + $3 }END{ print S }' )
echo "Total Credit = " $( grep "\|D\|" YourFile| awk -F"|" '{ S = S + $3 }END{ print S }' )

If the amount is very big , you will have to format the ouput .....
let me know the result..

Shihab

5

In one command:

awk -F'|' '{ if ($2 == "C") { c += $3; } if ($2 == "D") { d += $3; } } END {printf ("Credit: %d\n",c); printf ("Debit: %d\n",d);}' inputfile.txt
6

Thank you.

7

Unbeliever is correct. You don't need grep when you're using awk. Here's a shorter version of his program.

awk -F'|' '$2=="C"{c += $3} $2=="D"{d += $3} END{print "Credit:",c; print "Debit:",d}' inputfile.txt
8

Here's another solution that uses an array as a bucket accumulator:

awk -F'|' '$2 ~ /[CD]/ {
              credit_debit_array[$2]+=$3
           } 
           END {
               print "Credits:", credit_debit_array["C"]
               print "Debits:",  credit_debit_array["D"]
           }' inputfile.txt