Sum based on certain column


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Sum based on certain column
# 1  
Old 07-19-2013
Sum based on certain column

I have file 1

Code:
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    X3    X5    X7    X9
1/2/2013    B    833.9300537    457    20165    X2    X3    X6    X7    X9
1/3/2013    A    563.6917419    396    13879    X1    X3    X5    X7    X9
1/3/2013    B    632.0749969    169    21205    X2    X3    X6    X7    X9
1/4/2013    C    48.33055687    41    1596    X1    X4    X5    X8    X9

I want to sum value in column 3 based on column 6 and column 1
i expect the result

Code:
1/1/2013    X1    1012.909699
1/1/2013    X2    600.8333588
1/2/2013    X1    844.2973022
1/2/2013    X2    833.9300537
1/3/2013    X1    563.6917419
1/3/2013    X2    632.0749969
1/4/2013    X1    48.33055687

thanks for the help

Last edited by radius; 07-19-2013 at 01:15 PM..
# 2  
Old 07-19-2013
Sum column 2? That is an alphabetic character.

It looks like you want to do something like the SQL code:-
Code:
select Col1, Col6, sum(Col4)
from table_name
group by Col1,Col6
order by Col1,Col6 ;

Is this right?

There could be many ways of writing variations in shell, C, awk etc. What is your source from and what are you hoping to run it in?



Robin
Liverpool/Blackburn

---------- Post updated at 05:17 PM ---------- Previous update was at 05:15 PM ----------

Ah, an edit, now my message doesn't make sense.

So, the equivalent of:-
Code:
select Col1, Col6, sum(Col3)
from table_name
group by Col1,Col6
order by Col1,Col6 ;


So, can you confirm?



Robin
# 3  
Old 07-19-2013
sorry, i already edited and suppose to sum by column 3
# 4  
Old 07-19-2013
Quote:
Originally Posted by radius
I have file 1

Code:
1/1/2013    A    553.0763397    96    16582    X1    X3    X5    X7    X9
1/1/2013    B    459.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    X3    X5    X7    X9
1/2/2013    B    833.9300537    457    20165    X2    X3    X6    X7    X9
1/3/2013    A    563.6917419    396    13879    X1    X3    X5    X7    X9
1/3/2013    B    632.0749969    169    21205    X2    X3    X6    X7    X9
1/4/2013    C    48.33055687    41    1596    X1    X4    X5    X8    X9

I want to sum value in column 2 based on column 6 and column 4
i expect the result

Code:
1/1/2013    X1    1012.909699
1/1/2013    X2    459.8333588
1/2/2013    X1    844.2973022
1/2/2013    X2    833.9300537
1/3/2013    X1    563.6917419
1/3/2013    X2    632.0749969
1/4/2013    X1    48.33055687

thanks for the help
I think I can produce the output you want, but I can't reconcile the output you have shown with your description of what you want done. The sum of the A's, B's, and C's in column 2 doesn't seem to play any role in the output. The two lines that you seem to have combined (the 1st line and the 3rd line in your input) have matching values in columns 1 and 6; not 6 and 4.

Would a more accurate statement of your requirements be?
For every line in the input file where the 1st field and the 6th field are the same, print the 1st field, the 6th field, and the sum of the values in the 3rd field.
Does the order of the output lines matter? If it does, please describe the required order

Last edited by Don Cragun; 07-19-2013 at 01:22 PM.. Reason: Sum of 3rd field; not 4th.
# 5  
Old 07-19-2013
i mean i want to sum value in column 3 based on column 6 and column 1

for example :
I have "X1" in column 6 and there are 2 rows with column 1 = 1/1/2013..then sum its value from column 3 ( 553.0763397 + 459.8333588 = 1012.9096985)

I have "X2" in column 6 and there is only one row with column 1= 1/1/2013..then sum its respected value from column 3 = 459.8333588

thanks
# 6  
Old 07-19-2013
You might want to read this thread where i have covered exactly this problem.

I hope this helps.

bakunin
# 7  
Old 07-19-2013
Quote:
Originally Posted by bakunin
You might want to read this thread where i have covered exactly this problem.

I hope this helps.

bakunin
It isn't quite the same in this case because the input is not in sorted order and (since the month and day fields in the date are variable length [1 or 2 digits instead of always two digits with a leading 0 when needed] in the general case, the year is after the month and day, and there are different field separators needed to sort the parts of the date and the other field) the sort utility can't be used without a separate processing step to normalize the date.

If the OP doesn't care about the output order, this is trivial to do with awk. If the output needs to be sorted by year, month, day, and input column 6; the processing is more involved. I'm still waiting for radius to say whether or not output order matters.
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Sum in file based column

Hi All, I have a file as below and want to sum based on the id in the first column Input 10264;ATE; 12 10265;SES;11 10266AUT;50 10264;ATE;10 10265;SES;13 10266AUT;89 10264;ATE;1 10265;SES;15 10266AUT;78 Output 10264;ATE; 23 10265;SES;39 10266AUT;139 (6 Replies)
Discussion started by: arunkumar_mca
6 Replies

2. Shell Programming and Scripting

Sum of a column as new column based on header in a script

Hello, I am trying to store sum of a column as a new column inside a file but have to find the column names dynamically I/p c1,c2,c3,c4,c5 10,20,30,40,50 20,30,40,50,60 If i want to find sum only column c1, c3 and output it as c6,c7 O/p c1,c2,c3,c4,c5,c6,c7 10,20,30,40,50,30,70... (6 Replies)
Discussion started by: mkathi
6 Replies

3. UNIX for Dummies Questions & Answers

Match sum of values in each column with the corresponding column value present in trailer record

Hi All, I have a requirement where I need to find sum of values from column D through O present in a CSV file and check whether the sum of each Individual column matches with the value present for that corresponding column present in the trailer record. For example, let's assume for column D... (9 Replies)
Discussion started by: tpk
9 Replies

4. Shell Programming and Scripting

Sum column values based in common identifier in 1st column.

Hi, I have a table to be imported for R as matrix or data.frame but I first need to edit it because I've got several lines with the same identifier (1st column), so I want to sum the each column (2nd -nth) of each identifier (1st column) The input is for example, after sorted: K00001 1 1 4 3... (8 Replies)
Discussion started by: sargotrons
8 Replies

5. Shell Programming and Scripting

awk to sum a column based on duplicate strings in another column and show split totals

Hi, I have a similar input format- A_1 2 B_0 4 A_1 1 B_2 5 A_4 1 and looking to print in this output format with headers. can you suggest in awk?awk because i am doing some pattern matching from parent file to print column 1 of my input using awk already.Thanks! letter number_of_letters... (5 Replies)
Discussion started by: prashob123
5 Replies

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

7. Shell Programming and Scripting

Sum Of Column Based On Column Condition

I have a following inputfile MT,AP,CDM,TTML,MUM,GS,SUCC,3 MT,AP,CDM,TTSL,AP,GS,FAIL,9 MT,AP,CDM,RCom,MAH,GS,SUCC,3 MT,AP,CDM,RTL,HP,GS,SUCC,1 MT,AP,CDM,Uni,UPE,GS,SUCC,2 MT,AP,CDM,Uni,MUM,GS,SUCC,2 TTSL,AP,GS,MT,MAH,CDM,SUCC,20 TTML,AP,GS,MT,MAH,CDM,FAIL,10... (2 Replies)
Discussion started by: siramitsharma
2 Replies

8. Shell Programming and Scripting

Sum a column value based on multiple keys

Hi, I have below as i/p file: 5ABC 36488989 K 000010000ASB BYTRES 5PQR 45757754 K 000200005KPC HGTRET 5ABC 36488989 K 000045000ASB HGTRET 5GTH 36488989 K 000200200ASB BYTRES 5FTU ... (2 Replies)
Discussion started by: nirnkv
2 Replies

9. Shell Programming and Scripting

sum multiple columns based on column value

i have a file - it will be in sorted order on column 1 abc 0 1 abc 2 3 abc 3 5 def 1 7 def 0 1 -------- i'd like (awk maybe?) to get the results (any ideas)??? abc 5 9 def 1 8 (2 Replies)
Discussion started by: jjoe
2 Replies

10. UNIX for Dummies Questions & Answers

How do I sum one column based on another column?

Hi, I am new to this forum and new to awk. I have a file that contains 2 columns. Heres an example of what it looks like: 10 + 20 + 40 + 50 - 70 - So the file is tab-delimited. What I want to do is add 10 to column 1 whenever column 2 is + and substract 10 from column 1... (1 Reply)
Discussion started by: phil_heath
1 Replies
Login or Register to Ask a Question