MySQL: Add Statistics Column for the Number Count of Records from Another Table

• Tweet

• Tweet

It’s hard to describe this scenario precisely with limited title length. Basically, this is when you want to dedicate a field in ‘category‘ table to store the number of articles or posts in ‘post‘ table that fall into this category.

Previously, you had both ‘category’ table to store post categories and ‘post’ table to store posts. Each post must fall under a category for the sake of categorization referenced by a field named ‘category’ in table ‘post’ that bears the ID of the category in table ‘category’ that this post belongs to.

I hope I have made myself clear on this.

Problem

Now the problem is that you want to display the count of posts under each category and there are apparently a large number of them so preferably you don’t want to count through them all each time a web page is loaded so as to reduce mysql server load.

Solution

The solution is obvious. You’d make an additional column for table ‘category’ arbitrarily named ‘count‘ for example. And update it once there’s an addition to the posts or a deletion from the existing. Now how do we do that.

Assume that there are only 2 columns in the current ‘category’ table, one is ID and the other is TITLE. To create another category table from the existing one with an additional ‘count’ field:

CREATE TABLE category2 SELECT category.id AS id, category.title AS title, COUNT( post.category ) AS count
FROM category, post
WHERE post.category = category.id
GROUP BY post.category 

This would create a new table called ‘category2’ that’s no different from the original ‘category’ table except for the additional column we just added: ‘count‘.

Executing this single SQL query, not only is the new column created but they are also filled with the count of the posts that are assigned to each of the category.

The last task of this job will be to drop the original ‘category’ table and rename ‘category2’ to ‘category’.

Another Solution

By the help of an extra temporary table, you can also add a number / count column by combining the following 2 tips:

1. Create a temporary table of rows with id and count: counting records number.
2. Transfer the statistics from the temporary table to the newly created column of the original table: update more than one rows with a single update query.