2

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

lemon
  • 14,875
  • 6
  • 18
  • 38
BoggyB
  • 23
  • 5
  • 1
    [Why should I tag my rdbms](https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms) – Stu Jun 04 '23 at 08:48

1 Answers1

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.

lemon
  • 14,875
  • 6
  • 18
  • 38
  • 1
    I went for option 2 as I wasn't certain on the SQL syntax for the AFTER INSERT or for the ALTER TABLE piece either. After changing tab; to products it worked perfectly. Thank you very much. – BoggyB Jun 06 '23 at 08:12
  • Hello @lemon, I ve noticed this question and your solution creating a view. I created the trigger in Oracle without any errors but when i try to insert no way-trigger failed etc.I m using Oracle 21c and Sql Server 2019. Can you please create one in Oracle or Sql server? This is what i did in Oracle CREATE OR REPLACE TRIGGER update_cate_trigger before INSERT ON tab FOR EACH ROW DECLARE l_cate NUMBER; BEGIN SELECT COUNT(*) INTO l_cate FROM tab WHERE cat_id = :NEW.cat_id; UPDATE tab SET cate = l_cate WHERE prod_id = :NEW.prod_id; END; / – Florin Jun 08 '23 at 11:10
  • 1
    Craft a new post, and link this post as a similar one with respect to your current scenario. – lemon Jun 08 '23 at 11:11
  • @lemon ok thank you – Florin Jun 08 '23 at 11:14