How to calculate a sum of certain records?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to calculate a sum of certain records?
# 1  
Old 06-10-2005
How to calculate a sum of certain records?

Hi,
i have a file where the records are like this.

vt100 2048 D402 MG0010 0
586 262144 D403 MG0011 1000
486 8192 D404 MG0012 270
386 8192 A423 CC0177 40
586 65536 A424 CC0182 670
486 16384 A423 CC0183 100
486 16384 A425 CC0184 80
65000 4096 B407 EE1027 80

I want firstly,
to count how many times each combination of the first 2 letters from the 4th field exist and print it like this:
3 MG or MG 3
5 CC

I tried this :
Code:
awk -F"\t" '{print $4}' pcs.txt | uniq -c

but I didn't know how to count only the first two letters of the fied.

Secondly,
I want to sum the second field for each differrent entry of the 3rd field and print them. Like this
A423 24576
A425 16384

I tried this :
Code:
awk -F"\t" '{a[$3]=$3; 
for (i = 1; i <= NR; i++){
for (i = 1; i <= NR; i++){
if(a[++i] = $3){
size+=$2}
}
print a[i]" "size
}' pcs.txt

but somewhere I have a mistake and I can't find it and also i am not sure if this is going to give me what I want.

Please help me
# 2  
Old 06-10-2005
This should do it for your first counting part...

Code:
sed -n -e 's/.*\([A-Z][A-Z]\)[0-9]*.*/\1/p' pcs.txt | uniq -c

For your input, it gave,

3 MG
4 CC
1 EE

Basically, the scripts strips out every character except for the two upper-case characters. And the script assumes you always have input which obey the format you provided.

Vino
# 3  
Old 06-10-2005
Oops...

Got the wrong message in the wrong thread.

Vino

Last edited by vino; 06-10-2005 at 11:11 AM.. Reason: Posted in the wrong thread....
# 4  
Old 06-10-2005
Maybe I didn't explained it right. I need to find the sum for every different record of the 3rd field. So I need to sum the two numbers of the A423 and print the total.


PS Sorry for my English
# 5  
Old 06-10-2005
Quote:
Originally Posted by vino
This should do it for your first counting part...

Code:
sed -n -e 's/.*\([A-Z][A-Z]\)[0-9]*.*/\1/p' pcs.txt | uniq -c

It is working for the part of the file i gave in the beginning but it is not working in the whole file right. Probably uniq -c is counting the same lines that are one next to the other and not spread in the file. What I did to get the result I wanted is the use of sort.

Code:
sed 's/.*\([A-Z][A-Z]\)[0-9]*.*/\1/p' pcs.txt |sort| uniq -c

Do you know if instead of sed I can use awk to get the same result?
# 6  
Old 06-10-2005
I think for uniq to work properly, it needs a sort'ed list.

Not sure, tho'.

Vino
# 7  
Old 06-10-2005
for 2nd question:
Code:
awk -F"\t" '{a[$3]+=$2}END{for (i in a) print i,a[i]}'

[not test]
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to calculate difference of split and sum the difference

In the awk I am trying to subtract the difference $3-$2 of each matching $4 before the first _ (underscore) and print that value in $13. I think the awk will do that, but added comments. What I am not sure off is how to add a line or lines that will add sum each matching $13 value and put it in... (2 Replies)
Discussion started by: cmccabe
2 Replies

2. 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

3. Shell Programming and Scripting

Sum and calculate number in table

Hello everyone, I have some problem in calculation number using awk. input file format : S1 1 : 0.003 0.031 S2 1 : 0.020 0.095 S3 4 : 0.088 0.012 S4 2 : 0.010 0.090 S5 2 : 0.244 0.066 S6 3 : 0.249 0.751 S7 3 : 0.010 0.990 S8 3 : 0.230... (4 Replies)
Discussion started by: awil
4 Replies

4. Homework & Coursework Questions

Calculate sum of the field

Hi All, I need to calculat the sum of the particular field in text file. And the datatype of the field in file is decimal(18,2). If the file is small, then I am facing any problem. But the file is huge, then the result is converted into exponential format. I tried using various command thr... (0 Replies)
Discussion started by: lathanandhini
0 Replies

5. 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

6. Shell Programming and Scripting

Calculate age of a file | calculate time difference

Hello, I'm trying to create a shell script (#!/bin/sh) which should tell me the age of a file in minutes... I have a process, which delivers me all 15 minutes a new file and I want to have a monitoring script, which sends me an email, if the present file is older than 20 minutes. To do... (10 Replies)
Discussion started by: worm
10 Replies

7. Solaris

calculate sum size of files by date (arg list too long)

Hi, I wanted a script to find sum of files for a particular date, below is my script ls -lrt *.req | nawk '$6 == "Aug"' | nawk '$7 == "1"'| awk '{sum = sum + $5} END {print sum}' However, i get the error below /usr/bin/ls: arg list too long How do i fix that. Many thanks before. (2 Replies)
Discussion started by: beginningDBA
2 Replies

8. Shell Programming and Scripting

awk-calculate records of a text

I want to make a bash shell script that accepts as argument a file name (cars.txt) and: 1) calculates the total price per year and per model. 2) For each car that it's number starts with TK, I want to print the surname and name of the owner and the total cost for them. NBE3452... (3 Replies)
Discussion started by: Mark_orig
3 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

Column sum group by uniq records

Dear All, I want to get help for below case. I have a file like this. saman 1 gihan 2 saman 4 ravi 1 ravi 2 so i want to get the result, saman 5 gihan 2 ravi 3 like this. Pls help me. (17 Replies)
Discussion started by: Nayanajith
17 Replies
Login or Register to Ask a Question