How can I auto calculate a column in a table without using a view

Question

For example, I have the following table:

CREATE TABLE `test` (
  `total_results` int(10) unsigned NOT NULL,
  `num_results_as_expected` unsigned int(10) NOT NULL,
) ;

I would like to add another column to the table, without using a VIEW, to add the percent of :

(num_results_as_expected/total_results)*100

Is this possible without using a VIEW?

• Clarification. I know it's possible using a select statent, but I'd like to add this in the CREATE TABLE statement, so that the column will be available to anyone who accesses the table.

• Clarification 2: Is trigger the right way to do this? This will essentially store duplicate information in the column.

Answer 1

select (num_results_as_expected/total_results)*100 as percentage from test

So to get all the columns including the new one:

select total results, num_results_as_expected, (num_results_as_expected/total_results)*100 as percentage from test

This prevents you from having to store anything extra, the extra column magically appears when the data is fetched.

Answer 2

Create a regular column + add "before insert" trigger which will updated it's value with a calculated one on each insert operation. Ex.:

create trigger update_result before insert on test for each row
begin set new.result = (num_results_as_expected/total_results)*100;