I have the following tables (pseudo code).

// This table is for a list of farms
CREATE TABLE farms
farm_id INT (pk)
farm_name VAR
farm_nearest_town VAR

// This table is for a list of animals
CREATE TABLE animal_types
animal_types_id INT (pk)
animal_type_name VAR

// This table is to list how many animals of each type a farm has
CREATE TABLE farm_animal_types
farm_animal_types_id INT (pk)
farm_id INT (fk)
animal_types_id INT (fk)
number_of_this_animal_type_at_this_farm INT

What I would ideally like is to have an additonnal column in the farms table
called farm_total_animals which is the total number of every animal on the
farm. (eg if a farm 10 goats and 5 pigs then that is 15 farm_total_animals )

The thing is I don't know how to do this or if it is actually possible.

So far I've come up with this query:
SELECT farm_id, SUM(number_of_this_animal_type_at_this_farm) FROM
farm_animal_types GROUP BY farm_id

This is where I've ground to a halt and would like some advice on my best
path forward. I'm not altogether sure what I can and can't do with MySQL.
Years ago I used dBase (or was it Access ??) and I think you could have a
column in a table which was calculated. I was looking for something similar
in MySQL and found nothing similar but perhaps I was looking in the wrong
place.

As I am using MySQL 5, I understand VIEWS are supported so I could create a
VIEW based on that query above. Just not exactly sure how they will help.

Appreciate any feedback or pointers on what I should be looking for and
reading up. Some specific search terms that I plug into Google would be
great.

Cheers

Phil