How to Count the Number of Rows with the Same Value in SQL

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!

#NameTypeCollationAttribute
1idInt(11)  
2News_idInt(11)  
3News_titleVarchar (255)Latin1_swedish_ci 
4NameVarchar (255)Latin1_swedish_ci 
5News_descriptionTextLatin1_swedish_ci 
6DateDate  
7ViewInt (11)  
8Number of commentsInt (11)  

Then,  create an id for the connector, here we use post_id which will link to the news table

#NameTypeCollaction
1idInt (11) 
2Post idInt (11) 
3NameVarchar (255)Latin1_swedish_ci
4ReplyTextLatin1_swedish_ci
5StatusInt (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.

Optionsidnews_

 

id

News_titlenameNews_

 

description

DateViewNumber of comments
Edit Copy Delete11Updates: More Features ReleasedBrian FerryYou can replace all this text with your own text2017-11-03213
Edit Copy Delete22TitleShanenThis is content 237
Edit Copy Delete33This is titleLaugh of LoudsThis is test 110
Edit Copy Delete41LuL LEL LOLMarioYour princess is in another castle 00
Edit Copy Delete52AdventurerGuardI used to be an adventurer like you until I took 00
Edit Copy Delete63SoapCpt. PriceYou drop the soap, you pay the price 00
Edit Copy Delete71Water wyvern in the desertHunterOh boy, i hate this 00

Then, for the comments, here is the table:

OptionsidPost_idNameReplyStatus
Edit Copy Delete11ShanenIt is beautiful1
Edit Copy Delete22BrianFantastic1
Edit Copy Delete32SelenaGo ahead1
Edit Copy Delete42OmesFighting, dude1
Edit Copy Delete51ChessYou are not alone1
Edit Copy Delete62NynaThat’s great1

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.

idPost_idNameReplyStatusTotal
11ShanenIt is beautiful13
22BrianFantastic17
99TesttGreat job11

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.

OptionsidPost_idNameReplyStatus
Edit Copy Delete11ShanenIt is beautiful1
Edit Copy Delete22BrianFantastic1
Edit Copy Delete32SelenaGo ahead1
Edit Copy Delete42OmesFighting, dude1
Edit Copy Delete51ChessYou are not alone1
Edit Copy Delete62NynaThat’s great1
Edit Copy Delete72GommessSound great1
Edit Copy Delete82TomLol lol lol1
Edit Copy Delete99JohnWkwkwk1
Edit Copy Delete102WickyIt’s fabulous1
Edit Copy Delete111BluessGreatest thing1

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!

Leave a Reply

Your email address will not be published.