Adding autogenerated column that computes frequencies

Question

I would like to create a generated column in my SQL table products who's value is automatically calculated by COUNTING the frequency of values in another column.

Please see example below, prod_cat_id_count is the result I am after :

prod_id	prod_name	prod_cat_id	prod_cat_id_count
1	prod_name_1	1	2 - returns frequency of prod_cat_id
2	prod_name_2	1	2
3	prod_name_3	2	1
4	prod_name_4	3	3
5	prod_name_5	3	3
6	prod_name_6	3	3
7	prod_name_7	4	2
8	prod_name_8	4	2
9	prod_name_9	5	2
10	prod_name_10	5	2

Something like

ALTER TABLE products
ADD COLUMN prod_cat_id_count INT GENERATED ALWAYS AS (COUNT(prod_cat_id) VIRTUAL;

Thanks in advance for any help

Answer 1

There are two options for you to get out of this rabbit hole:

• create an AFTER INSERT trigger, that checks when a new record is inserted in your table and updates the frequencies (but syntax for this solution is very DBMS-dependant)
• create a view that computes your frequencies in a lazy way (great option if you have very frequent insert operations):

CREATE VIEW prod_frequencies AS 
SELECT prod_id,
       prod_name,
       prod_cat_id,
       COUNT(prod_name) OVER(PARTITION BY prod_cat_id) AS prod_cat_id_count
FROM tab;

When you need to show the updated frequencies, this will give you exactly what you need, given that your DBMS supports window functions.