In this chance, we are going to show you the number of rows with the same value in MySQL. In general, every database has the same function, namely to store data.
For this time, we are using the MySQL database, where to access data requires a programmer to know the database access language. In the case of MySQL, we usually call it the mysql query language, to process data and manipulate data in the MySQL database.
Method 1: How to Count the Number of Rows with the Same Value in SQL
In this case, we will try to provide an example of data, where in our database we have two tables, then each table has different fields. Between tables have a relation so that they are connected to each other, then we will calculate the amount of data with the same ID and it will be displayed. Immediately, we start implementing the number of rows with the same value in MySQL , create a database then create two tables in it
Let’s see how to count the number of rows with the same value in SQL!
| # | Name | Type | Collation | Attribute |
| 1 | id | Int(11) | ||
| 2 | News_id | Int(11) | ||
| 3 | News_title | Varchar (255) | Latin1_swedish_ci | |
| 4 | Name | Varchar (255) | Latin1_swedish_ci | |
| 5 | News_description | Text | Latin1_swedish_ci | |
| 6 | Date | Date | ||
| 7 | View | Int (11) | ||
| 8 | Number of comments | Int (11) |
Then, create an id for the connector, here we use post_id which will link to the news table
| # | Name | Type | Collaction |
| 1 | id | Int (11) | |
| 2 | Post id | Int (11) | |
| 3 | Name | Varchar (255) | Latin1_swedish_ci |
| 4 | Reply | Text | Latin1_swedish_ci |
| 5 | Status | Int (1) |
We create the names of the news and comment tables, then we will count the comments where the same post_id will be counted. Which means we will count the comments for each blog in the comment table. Then, try to fill in each table, we enter the data below.
| Options | id | news_
id | News_title | name | News_
description | Date | View | Number of comments |
| Edit Copy Delete | 1 | 1 | Updates: More Features Released | Brian Ferry | You can replace all this text with your own text | 2017-11-03 | 21 | 3 |
| Edit Copy Delete | 2 | 2 | Title | Shanen | This is content | 23 | 7 | |
| Edit Copy Delete | 3 | 3 | This is title | Laugh of Louds | This is test | 11 | 0 | |
| Edit Copy Delete | 4 | 1 | LuL LEL LOL | Mario | Your princess is in another castle | 0 | 0 | |
| Edit Copy Delete | 5 | 2 | Adventurer | Guard | I used to be an adventurer like you until I took | 0 | 0 | |
| Edit Copy Delete | 6 | 3 | Soap | Cpt. Price | You drop the soap, you pay the price | 0 | 0 | |
| Edit Copy Delete | 7 | 1 | Water wyvern in the desert | Hunter | Oh boy, i hate this | 0 | 0 |
Then, for the comments, here is the table:
| Options | id | Post_id | Name | Reply | Status |
| Edit Copy Delete | 1 | 1 | Shanen | It is beautiful | 1 |
| Edit Copy Delete | 2 | 2 | Brian | Fantastic | 1 |
| Edit Copy Delete | 3 | 2 | Selena | Go ahead | 1 |
| Edit Copy Delete | 4 | 2 | Omes | Fighting, dude | 1 |
| Edit Copy Delete | 5 | 1 | Chess | You are not alone | 1 |
| Edit Copy Delete | 6 | 2 | Nyna | That’s great | 1 |
We will count the most comments for each news id, by using this formula:
SELECT *, COUNT( * ) AS total FROM comment GROUP BY post_id
which means, taking all data then using the COUNT function (calculating) all the fields from the comment table then in the post_id column all will be grouped and calculated by COUNT based on post_id, the result will be like the image below.
| id | Post_id | Name | Reply | Status | Total |
| 1 | 1 | Shanen | It is beautiful | 1 | 3 |
| 2 | 2 | Brian | Fantastic | 1 | 7 |
| 9 | 9 | Testt | Great job | 1 | 1 |
Then, you can see the total number of comments which will be displayed as 7 for post_id 2, 3 for post_id 1 and 1 for post_id 9.
| Options | id | Post_id | Name | Reply | Status |
| Edit Copy Delete | 1 | 1 | Shanen | It is beautiful | 1 |
| Edit Copy Delete | 2 | 2 | Brian | Fantastic | 1 |
| Edit Copy Delete | 3 | 2 | Selena | Go ahead | 1 |
| Edit Copy Delete | 4 | 2 | Omes | Fighting, dude | 1 |
| Edit Copy Delete | 5 | 1 | Chess | You are not alone | 1 |
| Edit Copy Delete | 6 | 2 | Nyna | That’s great | 1 |
| Edit Copy Delete | 7 | 2 | Gommess | Sound great | 1 |
| Edit Copy Delete | 8 | 2 | Tom | Lol lol lol | 1 |
| Edit Copy Delete | 9 | 9 | John | Wkwkwk | 1 |
| Edit Copy Delete | 10 | 2 | Wicky | It’s fabulous | 1 |
| Edit Copy Delete | 11 | 1 | Bluess | Greatest thing | 1 |
That’s so easy to count the number of rows with the same value in SQL, isn’t it?
Method 2: How to Count the Number of Rows with SQL Using Count IF
You may wonder how to count the number of rows with SQL. Here’s the way to count it:
As we know, to count rows (rows) in a table, we use the COUNT function which will automatically count the number of rows (rows with NULL values will not be counted).
The criteria for counting rows can be done in 2 ways, namely by:
- COUNT (*) which will count all rows in all columns. The results will be taken from the column with the highest number of rows.
- COUNT (field_name) which will count the rows for a particular column.
Meanwhile, based on data retrieval, the COUNT function can be written with or without the DISTINCT clause, they are:
- COUNT to count all rows even if there is duplication of data.
- COUNT (DISTINCT …) to count unique rows (same data is not counted).
If you want to have the result for all values of NUM:
SELECT `NUM`, COUNT(*) AS `count`
FROM yourTable
GROUP BY `NUM`
- Or just for one specific:
SELECT `NUM`, COUNT(*) AS `count`
FROM yourTable
WHERE `NUM`=1
- For specific num:
SELECT COUNT(1) FROM YOUR_TABLE WHERE NUM = 1
- For all num:
SELECT NUM, COUNT(1) FROM YOUR_TABLE GROUP BY NUM
Or you can also try this Query
select NUM, count(1) as count
from tbl
where num = 1
group by NUM
–having count(1) (You condition)
Well, if our explanation above cannot solve your problem, you certainly can look for other ways from any sources. Good Luck!
AUTHOR BIO
On my daily job, I am a software engineer, programmer & computer technician. My passion is assembling PC hardware, studying Operating System and all things related to computers technology. I also love to make short films for YouTube as a producer. More at about me…
