EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL count()
 

MySQL count()

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated May 12, 2023

MySQL count()

 

 

Introduction to MySQL count()

The MySQL COUNT() function provides several records in the result set from a table when an SQL SELECT statement is executed. This function does not count the NULL values. The count function gives a BIGINT value. This aggregate function returns all rows or only rows matched to specified conditions; if there is no row that matches, it returns 0. Here, the Aggregate function is a function that calculates some values and returns only a single value. We can use COUNT() in many ways, but it is necessary to understand how it works. We can receive different result values according to how we use it.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax

The basic syntax for the count function is:

COUNT(*)
COUNT(expression)
COUNT( [DISTINCT] expression )

Explanation: The function has three forms explained as follows:

  • COUNT(*) function: It returns all the number of rows in a table returned by the SELECT query, including non-NULL, NULL, and duplicate rows from the table.
Note: * denotes ‘all’.
  • COUNT(expression) function: It provides the result of the expression counting the number of rows without NULL values.
  • COUNT(DISTINCT expression): It results in the number of rows containing non-NULL distinct values in the result set returned by the SELECT statement.

How does the COUNT() function work in MySQL?

The Count is an aggregate function that returns a single concise result working on a table’s entire set of rows.

The database contains multiple tables, each with different sizes and varying numbers of rows. We can find millions of records or more in tables like transaction tables. Therefore, monitoring the progress of tables and getting the count of records becomes a significant factor in the development and operations process.

Examples to Implement MySQL Count()

Let us say we have a table named Customers as a sample:

MySQL Count()1

Note: In SQL, all the queries are case-insensitive.

Example #1

Using the MySQL COUNT(expression) function to get all records from the table using an expression value that does not contain a NULL value. The SQL query is:

Code:

SELECT COUNT(City) FROM Customers;

Output:

MySQL Count()2

Example #2

For the same table, let us useMySQLCOUNT(*) function. Suppose we have executed the following statement:

Code:

SELECT COUNT(*) FROM Customers;

Output:

MySQL Count()3

Explanation: This count function will return the rows count from the result set, which may contain or not contain NULL values.

Example #3

Now we will discuss onMySQLCOUNT(DISTINCT expression):

Code:

SELECT COUNT(DISTINCT City) FROM Customers;

Output:

MySQL Count()4

Explanation: Then, in this function, the DISTINCT keyword will show the count for the records that are unique, not duplicated or repeated, and the values that are not NULL.

Example #4

We can also use the Count function like this MySQL COUNT() with GROUP BY. With the count(), we can use GROUP BY SQL clause so that it provides the number of records inside a group:

Code:

SELECT City, COUNT(*) FROM Customers GROUP BY City;

Output:

MySQL Count()5

Explanation: In the above example, count() with the GROUP BY keyword groups all distinct cities and returns the count of each one.

Example #5

We can also use MySQL Count() with The HAVINGClause in the MySQL statement. In the above example, we can add a Having clause to filter the result from the above query further:

Code:

SELECT City, COUNT(*) FROM Customers GROUP BY City HAVING COUNT(*) >1;

Output:

HAVING Clause

Explanation: Here, we have added count() with the HAVING clause, which results in the count of records from the table Customers GROUP BY City with a count greater than 1. The NULL value field is also counted.

Example #6

The Count() function can be combined with the Flow Control functions. You can associate Count() function with flow control functions to achieve better functionality.

For example, the flow control function IF() can be used with the expression that will be used for the Count() function in SQL Statement. This will be pretty supportive for quick data analysis inside a database. Let us consider the data from a table named Products with three fields ProductID, ProductName, Price, etc.:

Flow Control functions

Here is a field called Price in the table; every product has different prices recorded in that row. So, we can use this Price field to do the following query execution with the COUNT() and IF() functions in the SQL statement to return the result count:

Code:

SELECT
COUNT(IF(Price <=10,1,NULL)) 'Low',
COUNT(IF(Price BETWEEN 10 AND 30,1,NULL)) 'Medium',
COUNT(IF(Price > 30,1,NULL)) 'High'
FROM Products;

Output:

Price field

Explanation: From the above statement, we can see that based on the Price in the Products table, we have divided them into three groups named Low, Medium, and High. Here we have used the IF() function in combination with to count() function to compare the different prices of the products to a given condition, and when the condition is matched, then it returns the result as above, where the count for three groups; low, medium, high are divided that fulfills the expression demand. If there is no match to a condition, it will return a NULL value, which means 0.

Example #7

We can even use the UNION operator with the COUNT function to obtain the MySQL row count of two or more tables. Using UNION, we can unite the result sets gained from every SELECT statement and apply count to get the number of rows of multiple tables in a database.

For example, we have executed a single SQL query below to return the row count of two tables, Customers and Products, from our sample database:

Code:

SELECT     'Customers' tablename,   COUNT(*) rows FROM   Customers
UNION
SELECT     'Products' tablename,   COUNT(*) rows FROM  Products;

Output:

Using UNION

SELECT   COUNT(*) FROM   Customers
UNION
SELECT COUNT(*) FROM  Products;

MySQL Count()10

Example #8

We can use COUNT() in combination with the WHERE clause in the SELECT statement query if we want to count some table rows. Here, the number of expressions defined in the count function will be returned from the table based on particular criteria of the WHERE clause on the SELECT query. Let us take the previous Products table:

Code:

SELECT COUNT(ProductName) FROM Products WHERE SupplierID = 1;

Output:

WHERE clause

Conclusion

The SELECT statement execution utilizes the MySQL COUNT function, which counts the number of rows in a table that satisfy specific conditions. One can consider the MySQL COUNT function as the simplest and most advantageous function. In this article, we have also learned to return a non-NULL count of rows from one or multiple tables in the MySQL Database using various MySQL clauses and operators with syntax and examples.

Recommended Articles

We hope that this EDUCBA information on “MySQL count()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL avg()
  2. Commands in MySQl
  3. MySQL Aggregate Function
  4. MySQL Subquery

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW