sql - MySQL: Computed Column

Question

I'm just started using SQL and ran into a problem.

In my database, I presently have two tables, Cinemas and Theatres. I'm trying to create a column "# of Theatres# in the Cinemas table that counts the number of Theatres in the Theatres table with the same CinemaID (foreign key) as a Cinema in the Cinemas table. I got it to work as an SQL query:

SELECT cinemas.CinemaID,Town,COUNT(*) AS '# of Theatres'
FROM cinemax.cinemas,cinemax.theatres
WHERE cinemas.CinemaID=theatres.CinemaID
GROUP BY cinemas.CinemaID;

But wanted to know if it's possible to create a column in the Cinemas table, that automatically performs the above query and inserts the value into each row.

It is going to be a very small database so speed isn't really an issue, I just want to learn how to make such a computed column (if even possible).

Answer 1

A computed column normally means a value you can calculate per row. MySQL does not support that, but SQL Server does. For example, to store the sum of two columns permanently:

create table Table1 (a int, b int, c as a+b persisted)

However, you're looking to store an aggregate, that is, a value for a group of rows. MySQL and SQL Server don't support materialized views with an aggregate, but Oracle does:

create table Table1 (a int, b int);

create materialized view View1 as
select  a
,       count(*) as Cnt
from    Table1
group by
    a;

With MySQL however, the closest you can do is a cronjob that periodically populates a table:

truncate table Table1Summary;
insert Table1Summary (a, Cnt) select a, count(*) from Table1;

You can query the table like a materialized view; it will be as fast, but not guaranteed to be up to date.