Subtotal in UNIX

Please help me on below req

Data in file

ARIZONA HCPAZ 47
ARIZONA HCPAZCONT 3056
ARIZONA AZA 20
CALIFORNIA HC06 878
CALIFORNIA LC04 51
CALIFORNIA LC06 4039
CALIFORNIA HCPCACONT 4960
THE CAMDEN GROUP CAM 83
TO BE DETERMINED TBD 3
TO BE DETERMINED 8-J 0
TO BE DETERMINED AS0 0

output I need:

ARIZONA HCPAZ 47
ARIZONA HCPAZCONT 3056
ARIZONA AZA 20

ARIZONA_SUB_TOTAL : 3,123

CALIFORNIA HC06 878
CALIFORNIA LC04 51
CALIFORNIA LC06 4039
CALIFORNIA HCPCACONT 4960

CALIFORNIA_SUB_TOTAL : 9,928

THE CAMDEN GROUP CAM 83
TO BE DETERMINED TBD 3
TO BE DETERMINED 8-J 0
TO BE DETERMINED AS0 0

OTHER_SUB_TOTAL :86

Thanks in advance

We are here to help you, not to do your work for you. What have you tried?

You did a poor job of explaining your data. For example, how do you decide what is "OTHER"?

Finally, use code tags for code and data samples.

Regards,
Alister

What is the delimiter? If it is space delimited and you have spaces in the first field, then you won't be able to properly parse the file. You should use either : or ~ to delimit the fields. If you change the delimiter you can probably use cut such as

for line in `cat data.dat`
do
echo "column 1: "+`echo ${line} | cut -d":" | -f1`
echo "column 2: "+`echo ${line} | cut -d":" | -f2`
echo "column 3: "+`echo ${line} | cut -d":" | -f3`
done

I have not yet run the above code to check for bugs.
You will need to take the logic and change it for what you need.

if it is not california and arizona then it its others

But you still need to know where the second and third fields are. You might be able to do a for loop, check for CA and AZ first with a case statement and have the else be grab the last word for the third column and the second to last word for the second column and hard code other for the first column.

it is comma deleimter

There is not a single comma in your sample input data.

Please stop using icode for everything you type. Use code only for code and data.

I recommend that you start over with data that actually reflects what you're telling us.

And, finally, what have you tried to solve the problem?

Regards,
Alister