Member Avatar for apanimesh061

I have a table ...
Ratings (userid, movieid, ratings, time)
where ...
select count(*) from ratings where userid = X; (No of ratings of a particular user)

Here, X ranges from 1-6040!

How should I get the count of each user and then store it in another table 'Users' in the attribute 'countUsers' ?

Please help !

  • 3 Contributors
  • 2 Replies
  • 132 Views
  • 20 Hours Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

asked how to save per-user rating counts; suggested populating a table and recommended a view. Both are valid but serve different needs: a view gives always-current counts, a stored column or summary table gives faster reads at the cost of maintenance.

If the Users table should hold counts persistently, add a proper column and an index, then populate it in one go and keep it in sync. Example schema/one-time populate steps:

ALTER TABLE Users ADD COLUMN countUsers INT UNSIGNED DEFAULT 0;
CREATE INDEX idx_ratings_userid ON ratings(userid);

UPDATE Users u
SET countUsers = (
  SELECT COUNT(*) FROM ratings r WHERE r.userid = u.userid
);

For ongoing maintenance (avoid recomputing for every read), maintain the summary with triggers that increment/decrement on rating changes. Example trigger patterns:

CREATE TRIGGER ratings_after_insert
AFTER INSERT ON ratings
FOR EACH ROW
BEGIN
  INSERT INTO Users(userid,countUsers) VALUES (NEW.userid,1)
  ON DUPLICATE KEY UPDATE countUsers = countUsers + 1;
END;
CREATE TRIGGER ratings_after_delete
AFTER DELETE ON ratings
FOR EACH ROW
BEGIN
  UPDATE Users SET countUsers = countUsers - 1 WHERE userid = OLD.userid;
END;

Notes and cautions: ensure Users.userid is UNIQUE, index ratings.userid, and be aware triggers add overhead and can complicate bulk loads — for large imports prefer disabling triggers and doing a single refresh pass. If absolute real-time accuracy is not required, a scheduled refresh (MySQL EVENT or cron job) or a read-optimized summary table is often simpler than complex trigger logic. For always-live but potentially slower results, use a view. See MySQL docs on CREATE VIEW and Triggers for syntax and behaviour: CREATE VIEW, Triggers, and INSERT ... ON DUPLICATE KEY UPDATE.

Recommended Answers

All 2 Replies

Member Avatar for aquilax

If you want to store the result into empty table:

INSERT INTO Users 
SELECT userid, count(*) 
FROM ratings 
GROUP BY userid
Member Avatar for debasisdas

Creating view is a better option for that.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.