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!

Answer 1

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