Login or Register to Ask a Question and Join Our Community


UNIX for Beginners Questions & Answers

Sum the total based on ID

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Sum the total based on ID
# 1  
Old 03-23-2018
Sum the total based on ID
Hi

I am having a set of files which will have the card details and the amount that was spending on the card. Each file will have the set of cards and dollar amount spend on it. I am trying to sum the dollar values by card number on each files. Is there a way I do it all in all one steps
Code:
File 1:
4040011001;1;293;;;;;;;5450858.41;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011001;2;293;;;;;;;2244585.18;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011002;1;293;;;;;;;12117.55;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011003;1;293;;;;;;;650117.79;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011003;2;293;;;;;;;282250.40;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011003;3;293;;;;;;;34665.34;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011003;5;293;;;;;;;174916.29;;;;;;;;;;;;;;;;;;;;;;;;;;
File 2
4040011001;1;293;;;;;;;800990858.41;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011001;2;293;;;;;;;228999585.18;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011002;1;293;;;;;;;121232.05;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011003;1;293;;;;;;;650232.79;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011003;2;293;;;;;;;2822232.00;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011003;3;293;;;;;;;34.34;;;;;;;;;;;;;;;;;;;;;;;;;;
4040011003;5;293;;;;;;;14916.93;;;;;;;;;;;;;;;;;;;;;;;;;;

I will be having file file1,file2,file3 and upto 126 in the directory.


Output I want is sum the card and the dollars like below
Code:
4040011001 --   1037685887
 (5450858.41 +2244585.18+800990858.41+228999585.18).
also for 4040011002,4040011003

Things I tried.

Code:
 grep ^4040011001 file1 | cut -d  -f10 | paste -sd+ | bc

This will give me the individual sum. I want to calculate all card id with out doing a individual grep
# 2  
Old 03-23-2018
Try
Code:
awk -F\; '{SUM[$1]+=$10} END {for (s in SUM) print s, "--", SUM[s]}' OFMT="%.2f" file[12]
4040011001 -- 1037685887.18
4040011002 -- 133349.60
4040011003 -- 4629365.88

These 2 Users Gave Thanks to RudiC For This Post:
arunkumar_mca rbatte1
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help with calculate the total sum of record in column one

Input file: 101M 10M10D20M1I70M 10M10D39M4I48M 10M10D91M 10M10I13M2I7M1I58M 10M10I15M1D66M Output file: 101M 101 0 0 10M10D20M1I70M 100 1 10 10M10D39M4I48M 97 4 10 10M10D91M 101 0 10 10M10I13M2I7M1I58M 88 13 0 10M10I15M1D66M 91 10 1 I'm interested to count how many total of... (6 Replies)
Discussion started by: perl_beginner
6 Replies

2. UNIX for Dummies Questions & Answers

Sum based on certain column

I have file 1 1/1/2013 A 553.0763397 96 16582 X1 X3 X5 X7 X9 1/1/2013 B 600.8333588 195 11992 X2 X3 X6 X7 X9 1/1/2013 B 459.8333588 195 11992 X1 X3 X6 X7 X9 1/2/2013 A 844.2973022 306 19555 X1 ... (12 Replies)
Discussion started by: radius
12 Replies

3. UNIX for Dummies Questions & Answers

Sum based on column 1

i have file input aaa ccc,45567,rterw,1 bbb dcs,564543,hjghgh,1 aaa ccc,454,rterw,6 i want to sum based on column 1 expected output aaa ccc,7 bbb dcs,1 (4 Replies)
Discussion started by: radius
4 Replies

4. Shell Programming and Scripting

Why sum of recs in awk don't match total rec count?

I'm using awk to determine if a field starting in position 604 for length of 10 is not equal to ALL spaces. It's searching several files which are in the current directory. The below awk indicates that there are 84 records on all files where this field IS NOT equal to ALL spaces ( there are 10... (2 Replies)
Discussion started by: mjf
2 Replies

5. UNIX for Dummies Questions & Answers

sum values based on ID

Hi, I would like to be able to sum up the counts of a column by the ID of another column. Example (although the actual file I have has thousands of IDs): Input file: A1BG-AS1:001 3 A1BG-AS1:002 0 A1BG-AS1:003 2 A1CF:001 1038 A1CF:002 105 A1CF:003 115 A1CF:004 137 Desired output... (3 Replies)
Discussion started by: fadista
3 Replies

6. Shell Programming and Scripting

Help with sum total number of record and total number of record problem asking

Input file SFSQW 5192.56 HNRNPK 611.486 QEQW 1202.15 ASDR 568.627 QWET 6382.11 SFSQW 4386.3 HNRNPK 100 SFSQW 500 Desired output file SFSQW 10078.86 3 QWET 6382.11 1 QEQW 1202.15 1 HNRNPK 711.49 2 ASDR 568.63 1 The way I tried: (2 Replies)
Discussion started by: patrick87
2 Replies

7. Shell Programming and Scripting

Help with total up based on same column info

Input file data US 100.25 data ENG 235.01 data US 23.12 data ENG 23.01 END UK 230.50 END Russia 20.00 . . Desired output data US 123.37 data ENG 258.02 END UK 230.50 END Russia 20.00 . . (1 Reply)
Discussion started by: perl_beginner
1 Replies

8. Shell Programming and Scripting

Help with calculate total sum of same data problem

Long list of input file: AGDRE1 0.1005449050 AGDRE1 2.1005443435 AGDRE1 1.2005449050 AGDRE1 5.1005487870 AASFV3 50.456304789 AASFV3 2.3659706549 AASFV3 6.3489807860 AASFV3 3.0089890148 RTRTRS 5.6546403546 . . Desired output file: AGDRE1 8.5021829410 AASFV3 62.180245240... (2 Replies)
Discussion started by: perl_beginner
2 Replies

9. Shell Programming and Scripting

Calculate total sum from a file

The file content is dynamic and using this format: name1 number1 name2 number2 name3 number3 name4 number4 .................... Need a smooth way to calculate the sum of all the numbers in that file (number1 + number2 + number3 + number4........ = total ) (11 Replies)
Discussion started by: TehOne
11 Replies

10. Shell Programming and Scripting

sum total by column

Hi, i have a file which content the following: >cat cols data a:23:data data b:76:data data c:-30:data i would like to sum up the value of column 2, but the result that return to me is 0. Can anyone help? i'm using this code to do the sum awk -F" " 'BEGIN {x=0} {x+=$2} END {print... (5 Replies)
Discussion started by: thh
5 Replies
Login or Register to Ask a Question