Data redundancy occurs for a lot of reasons. Several of the complicated duties you should cope with while working with database systems is trying to discover duplicate values. For this purpose, We will be using the COUNT() aggregate method. The COUNT() method returns the sum of rows residing in a specific table. The COUNT() function permits you to sum all rows or only rows matching the condition defined. In this guide, You’ll get to know how to identify duplicate values for one or maybe more MySQL columns using COUNT(). The COUNT() method has the following three types:
- COUNT(*)
- COUNT(expression)
- COUNT(DISTINCT expression)
Make definite that you have MySQL installed on your system. Open the MySQL command-line client shell and enter your password to continue. We will be looking at some examples for counting the matching values using the COUNT() method.
We have a table ‘social’ in our schema ‘data’. Let’s check its record via the following query.
MySQL COUNT(*)
The COUNT(*) method is used to count the number of rows residing in the table or count the number of rows according to the given condition. To check the total number of rows in a table, ‘social’ try the below query. We have a total of 15 rows in the table as per the result.
Take a glimpse of the COUNT(*) method while defining some conditions. We have to fetch the number of rows where the username is the same as ‘Mustafa’. You can see we have only 4 records for this particular name.
To fetch the total sum of rows where users’ website is ‘Instagram’, try the below-stated query. The table ‘social’ has only 4 records for the website ‘Instagram’.
To retrieve the total number of rows where the ‘Age’ is greater than 18 is as follows:
Let’s fetch the data of columns ‘User’ and ‘Website’ from a table, where the User name starts with the alphabet ‘M’. Try the below instruction on the shell.
MySQL COUNT(expression)
In MySQL, the COUNT(expression) method is only used when you want to count non-Null values of the column ‘expression’. The ‘expression’ would be the name of any column. Let us take a simple example of it. We have been only counting the non-null values of a column ‘Website’, which is related to the column ‘Age’ having a value that equals ‘25’. See! We have only 4 non-null records for the users having age ‘25’, who are using websites.
MySQL COUNT(DISTNCT expression)
In MySQL, the COUNT(DISTINCT expression) method is used to sum non-Null values and distinct values of the column ‘expression’. To count a distinct number of non-null values in the column ‘Age’ we have been using the below query. You will find 6 non-null and distinct records of column ‘Age’ from the table ‘social’. This means we have a total of 6 people having different ages.
MySQL COUNT(IF(expression))
For large emphasis, you should merge COUNT() with flow control functions. For starters, for a portion of the expression being used in the COUNT() method, you might use the IF() function. It may be very useful to do this to provide a fast breakdown of the information inside a database. We will be counting the number of rows with different age conditions and dividing them into three different columns, which can be said as categories. First, COUNT(IF) will be counting the rows having age less than 20 and save this count into a new column named ‘Teenage’. Second COUNT(IF) is counting the rows having ages between 20 and 30 while saving it to a column ‘Young’. Third, the last counts the rows having ages greater than 30 and saved into a column ‘Mature’. We have 5 teenagers, 9 young and only 1 mature person in our record.
MySQL COUNT(*) with GROUP BY Clause
The GROUP BY statement is a SQL instruction using for group rows with the same values. It returns the total number of values residing in each group. For instance, if you want to check each user’s number separately, you have to define the column ‘User’ with the GROUP BY clause while counting records for each user with COUNT(*).
You can either select more than two columns while performing the counting of rows along with the GROUP BY clause, as follows.
If we want to count rows while using the WHERE clause having some conditions in it alongside the GROUP BY and COUNT(*), you can also do that. The below query will fetch and count the records of columns: ‘User’, ‘Website’, and ‘Age’ where the website value is ‘Instagram’ and ‘Snapchat’ only. You can see we have only 1 record for both websites for different users.
MySQL COUNT(*) with GROUP BY and ORDER BY Clause
Let’s try the GROUP BY and ORDER BY clauses jointly with COUNT() method. Let’s fetch and count the rows of table ‘social’ while arranging the data in descending order using this query:
The below-stated query will first count the rows, then display the only records having COUNT greater than 2 in ascending order.
Conclusion
We have gone through all the possible methods to count the matching or duplicate records using the COUNT() method with different other clauses.
