Create a generated column in MySql with count

Question

I am not a database guy, despite this I have created a statement which counts the ids function by the unique group ids from the table as this:

USE farm;
SELECT reg.grpId, COUNT(reg.id) as TOTAL FROM farm.reg group by reg.grpId;

Because I do't want to operate over the NodeJs server I need to know if it is possible to make a generated column as like the below which gives me an error 1064 -SELECT not valid at this position

the statement:

USE farm;
ALTER TABLE reg ADD total INT GENERATED ALWAYS AS(SELECT reg.grpId COUNT(reg.id) FROM farm.reg group by reg.grpId)STORED AFTER grpId;

Thank you!

That's the case, you cannot do as you wish to do and you shouldn't store data that can be calculated on demand. — P.Salmon, Apr 17 '20 at 13:50
See the [docs](https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html): "Subqueries are not permitted." — Paul Spiegel, Apr 17 '20 at 13:54
Ok, got it! but there is any workaround, I need just the concept or steps (e.g. create another table to store the first & valid statement and after which you make a whatever..), not the code itself — A B, Apr 17 '20 at 13:59

score 1 · Accepted Answer · answered Apr 17 '20 at 13:58

You can't do what you want with a computed column. I would recommend a view and window functions (available in MySQL 8.0)

create view reg_view as
select r.*, count(*) over(partition by grpId) total
from reg r

In MySQL < 8.0, an option is to join with an aggregate query:

create view reg_view as
select r.*, g.total
from reg r
inner join (select grpId, count(*) total from reg group by grpId) g on g.grpId = r.grpId

Create a generated column in MySql with count

1 Answers1