Newbie on SQL -- having trouble using Window Functions

Question

I have a simple table in mysql which has a column called CAGR (type: DECIMAL).

My intent is to generate always a new column that computes the average of CAGR on each row (will be the same all the way down). I can't seem to figure it out.

First I test that I can do it with a SELECT Statement -- I can -- Here is the statement I used:

SELECT Name,
       AVG(CAGR) OVER () "avg_cagr"
FROM Table;

I ran the above, and it works. I get the header (Name and avg_cagr) and the Name and the same avg_cagr for each of my rows. Great.

Next, I'm ready to create my new column using GENERTAED AS. Here's what I wrote:

ALTER TABLE Table
ADD COLUMN CAGR_AVG DECIMAL(12,2)
    GENERATED ALWAYS AS (AVG (CAGR) OVER ()) STORED NULL;

This does not work. I get the following response:

Error Code: 3593. You cannot use the window function 'avg' in this context.'

Why?

Answer 1

In a virtual column you cannot use information from other rows. One solution is to use a VIEW.

CREATE TABLE myTable(
CAGR DECIMAL(5,2),
Name VARCHAR(10));

?

INSERT INTO myTable VALUES
(2.5,'one'),
(7.9,'two'),
(15,'three');

?

CREATE VIEW myView AS
SELECT 
  Name, 
  CAGR,
  AVG (CAGR) OVER () "avg_cagr" 
FROM myTable;

?

SELECT * FROM myView;

Name  |  CAGR | avg_cagr
:---- | ----: | -------:
one   |  2.50 | 8.466667
two   |  7.90 | 8.466667
three | 15.00 | 8.466667

db<>fiddle here