Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to add subtotal and total according 3rd field mentioned below table?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to add subtotal and total according 3rd field mentioned below table?
# 8  
Old 10-15-2015
Quote:
Originally Posted by RudiC
And, subtotals don't look consistent, and the total is missing.
Write shell script above mentioned table output should be below mentioned table
Please arrange the record group wise then subtotal no1 and no2 name wise and then grand total no1 and no2 name wise.Ignore the any separotor consider only pipe
please let me know if u have doubt.
# 9  
Old 10-15-2015
Quote:
Originally Posted by udhal
Write shell script
No! YOU write the script and we help you where you need help! This is the way things work here.

So, for a starter, i have described your exact problem here, so you might want to start reading and adapting what is there for your needs.

I hope this helps.

bakunin
# 10  
Old 10-15-2015
Code:
sort -t "|" file |awk -F "|" 'a!~$1{print x}a=$1'

please add subtotal in blank line according to name wise each name wise
Last edited by Don Cragun; 10-16-2015 at 03:24 AM.. Reason: Add CODE tags.
# 11  
Old 10-16-2015
Your sort is missing the key to sort by (unless you want to sort from the start of line), and your awk just prints an empty line when field 1 (not if "name", i.e. field 3) changes and doesn't do any totals. BTW, how do we know "ASH" and "SHA" belong together, i.e. are to be grouped, as well as "FL" and "KL"?

As an interim step, try
Code:
sort -t "|" -k3,3 file1 | awk -F\| '
FNR==1  {TMP=$3
        }
TMP!=$3 {print "subtotal", TMP, SUM1, SUM2; SUM1=SUM2=0
        }
        {TMP=$3
         SUM1+=$4
         SUM2+=$5
         TOT1+=$4
         TOT2+=$5
        }
1
END     {print "subtotal", TMP, SUM1, SUM2; SUM1=SUM2=0
         print "   total","   ", TOT1, TOT2
        }
' OFS="|"
115|AKKK|ASH|10|15
115|AKKK|ASH|20|20
115|AKKK|ASH|30|35
115|AKKK|ASH|30|35
subtotal|ASH|90|105
112|ABC|FL|15|15
112|ABC|FL|25|20
112|ABC|FL|25|45
subtotal|FL|65|80
112|ABC|KL|15|15
112|ABC|KL|20|25
subtotal|KL|35|40
111|AKKK|SHA|10|45
111|AKKK|SHA|15|35
111|AKKK|SHA|20|25
subtotal|SHA|45|105
   total|   |235|330

# 12  
Old 10-16-2015
below output should be display
First 3 filed will be display unique and last two field should be concatenate

Code:
115|AKKK|ASH|10|20|30|30|15|20|35|35
112|ABC|FL|15|25|25|15|20|45

Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules when displaying sample input, sample output, and sample code segments.
Last edited by Don Cragun; 10-16-2015 at 06:10 PM.. Reason: Add CODE tags.
# 13  
Old 10-16-2015
Quote:
Originally Posted by udhal
below output should be display
First 3 filed will be display unique and last two field should be concatenate

115|AKKK|ASH|10|20|30|30|15|20|35|35
112|ABC|FL|15|25|25|15|20|45
this looks like a new topic. Are we done with the original one?
If so, please show your OWN attempt and tell us where EXACTLY YOU are stuck.
Last edited by vgersh99; 10-16-2015 at 06:46 PM..
# 14  
Old 10-16-2015
Hi RudiC Appreciate ur replay now i can able to write a script as per requirement but the out put display three extra garbage line at the top which i removed tail+3
is it possible to add code insert line totalA=ASH+FL and totalb=KL+SHA
Could u please little bit describe what is two print statement and {},{} and
because i am in new in awk programming
sorry for one for new requirement
Code:
ACN|NAME|CITY|CNT|NO1|NO2
115|AKKK|ASH|IND|10|15
115|AKKK|ASH|IND|20|20
115|AKKK|ASH|IND|30|35
115|AKKK|ASH|IND|30|35
112|ABC|FL|USA|15|15
112|ABC|FL|USA|25|20
112|ABC|FL|USA|25|45

i have written shell script using cut command
and awk programming getting error
Code:
sed -n '2,$p' test > test1
for j in `cat test1|cut -d "|" -f1|uniq`
do
a=" "
b=" "
for i in `cat test1`
do
  if [ `echo $i|cut -d "|" -f1` -eq $j ]; then
 no1=`echo $i|grep "$j"|cut -d "|" -f5`
 no1=`echo $i|grep "$j"|cut -d "|" -f6`
 acn=`echo $i|cut -d "|" -f1`
 name=`echo $i|cut -d "|" -f2`
 city=`echo $i|cut -d "|" -f3`
cnt=`echo $i|cut -d "|" -f3`
a=$a$no1"|"
b=$b$no2"|"
fi
done
echo $acn "|" $name "|" $city "|" $cnt "|" $a "|" $b
done
-------
awk '
  {print $1 FS $2 FS $3
             
        }
        {IX=$1 FS $2 FS $3
         MAX[IX]=MAX[IX] DL[IX] $4
         MIN[IX]=MIN[IX] DL[IX] $5
         DL[IX]="|"
        }
END     {for (m in MAX) print m, MAX[m], MIN[m]}
' FS="|" file

below output should be display
First 4 filed will be display unique and last two field should be concatenate(no1)$(no2)
Code:
115|AKKK|ASH|IND|10|20|30|30|15|20|35|35
112|ABC|FL|USA|15|25|25|15|20|45

Appreciate ur replay
Last edited by vgersh99; 10-16-2015 at 07:14 PM.. Reason: code tags, please!
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 total and percent off field in file

Trying to use awk to print the lines in file that have either REF or SNV in $3, add a header line, sort by $4 in numerical order. The below code does that already, but where I am stuck is on the last part where the total lines are counted and printed under Total_Targets, under Targets_less_than is... (4 Replies)
Discussion started by: cmccabe
4 Replies

2. Shell Programming and Scripting

Calculate the total 4 field based on the conditions

Please help me to write a script Match with ACNO & NAME if it matched calculate the total val1 val2 val3 and val4 and GT is total of ACNO wise.please check the output Table ----------------- 1005|ANDP|ACN|20|50|10|30 1005|ANDP|ACN|20|10|30|40 1001|AND|NAC|40|50|40|50... (22 Replies)
Discussion started by: kalia4u
22 Replies

3. Shell Programming and Scripting

Need the output in the mentioned Table format

Hi Friends, I have the script output like below: Script Output: ----------------------------------------------------------------------- Details of the Client: ----------------------- name: server1; save set: All; ... (3 Replies)
Discussion started by: akmani
3 Replies

4. Solaris

3rd field in /etc/shadow

Searched and searched, but could not find a official answer. In Solaris, on a new server build, all the system accounts look like this: # cat /etc/shadow root:#########:6445:::::: daemon:NP:6445:::::: bin:NP:6445:::::: sys:NP:6445:::::: adm:NP:6445:::::: lp:NP:6445::::::... (6 Replies)
Discussion started by: flyddw
6 Replies

5. Shell Programming and Scripting

Check a field in a table

I have made a table PRD_WORK_LM.test and it contains one field, ctrl_test. This field contains a 0 or a 1. I want to write a unix script that goes like this: IF ctrl_test = 1 THEN ... ELSE exit FI How can I write this in a script? Do I have to do this within bteq? or outside bteq? can... (5 Replies)
Discussion started by: katled
5 Replies

6. Shell Programming and Scripting

only print line if 3rd field is 01

Similar question... I have a space delimited text file and I want to only print the lines where the 3rd word/field/column is equal to "01" awk '{if $3 = "01" print $0}' something like this. I meant to say: only print line IF 3rd field is 01 (2 Replies)
Discussion started by: ajp7701
2 Replies

7. Shell Programming and Scripting

Adding total of first field for each number in the second field

Dears, I need a script or command which can find the unique number from the second filed and against that number it adds the total of first field . 17215630 , 0 907043 ,1 201050 ,10 394149 ,4 1964 ,9 17215630, 0 907043 ,1 201050, 10 394149 ,4 1964 ,9 1234234, 55 23 ,100 33 ,67 ... (2 Replies)
Discussion started by: shary
2 Replies

8. Shell Programming and Scripting

Total records in table

Hi, I am having two tables. A & B I want to know the total number of records in each table and need to store each value in some variables to process further in the code. How it can be done ? With Regards (2 Replies)
Discussion started by: milink
2 Replies

9. Shell Programming and Scripting

Deleting every 3rd field using awk

I have a file whose format is like the following 350,2,16.2,195,2,8.0 every 3rd column of this file should be deleted. How can i achieve this tried with the following iostat -D -l 2 | /usr/xpg4/bin/awk ' NR>2 { for (i=0;i<=NF;i++)if(i%3==0)$i=""};' but no luck (3 Replies)
Discussion started by: achak01
3 Replies

10. Shell Programming and Scripting

Get the total of a field in all the lines of a group

Hi I have a Fixed format data file where I need to to get the total of the field at certain position in a file for a group of lines. In this data file I need the total of all the field ats position 30:39 for each line starting with 6 and for each group startign with 5. Which means for... (27 Replies)
Discussion started by: appsguy616
27 Replies
Login or Register to Ask a Question