MySQL COUNT() function

COUNT() function

MySQL COUNT() function returns a count of a number of non-NULL values of a given expression. It's a fundamental and versatile function that provides valuable insights into your data.

If it does not find any matching row, it returns 0.

This function is useful in -

• The COUNT() function can be used to validate that certain data meets certain criteria or conditions.
• COUNT() helps in generating accurate and informative reports.
• The COUNT() function is often used in conjunction with SUM(), AVG(), and MAX() that allows to perform complex analyses and calculations on your data.
• When dealing with large datasets, it's more efficient to retrieve just the count of rows rather than fetching all the rows themselves.
• COUNT() provides valuable data for analysis and decision-making.
• You can track changes in data over time by using COUNT() in combination with timestamps or date ranges.

Syntax

COUNT(expr);

Where expr is an expression.

MySQL Version: 8.0

Example : MySQL COUNT() function

The following MySQL statement will return the number of rows in author table.

Sample table: author

Code:

SELECT COUNT(*)
FROM author;

Output:

mysql> SELECT COUNT(*)
    -> FROM author;
+----------+
| COUNT(*) |
+----------+
|       15 | 
+----------+
1 row in set (0.00 sec)

Example : MySQL COUNT() with logical operator

The following MySQL statement returns the number of publishers for USA and UK. The WHERE clause filters the rows for the country USA and UK. Grouping is performed on country and pub-city columns by GROUP BY and then COUNT() counts a number of publishers for each groups.

Sample table: publisher

Code:

SELECT country,pub_city,COUNT(*)
FROM publisher
WHERE country='USA' OR country='UK' 
GROUP BY country,pub_city;

Output:

mysql> SELECT country,pub_city,COUNT(*)
    -> FROM publisher
    -> WHERE country='USA' OR country='UK' GROUP BY country, pub_city;
+---------+-----------+----------+
| country | pub_city  | COUNT(*) |
+---------+-----------+----------+
| UK      | Cambridge |        1 | 
| UK      | London    |        1 | 
| USA     | Houstan   |        1 | 
| USA     | New York  |        2 | 
+---------+-----------+----------+
4 rows in set (0.00 sec)

Pictorial Presentation

MySQL COUNT() using multiple tables

The following MySQL statement retrieves those rows from publisher table whose 'pub_id' in publisher table match the 'pub_id' in 'book_mast' table.

A grouping operation is performed on pub_id column of publisher table by GROUP BY and then number of times pub_id exists in publisher table is counted by COUNT().

Sample table : book_mast

Sample table: publisher

Code:

SELECT publisher.pub_name,COUNT(*)
FROM publisher,book_mast
WHERE publisher.pub_id=book_mast.pub_id
GROUP BY publisher.pub_id;

Output:

mysql> SELECT publisher.pub_name,COUNT(*)
    -> FROM publisher,book_mast
    -> WHERE publisher.pub_id=book_mast.pub_id
    -> GROUP BY publisher.pub_id;
+------------------------------+----------+
| pub_name                     | COUNT(*) |
+------------------------------+----------+
| Jex Max Publication          |        2 | 
| BPP Publication              |        2 | 
| New Harrold Publication      |        2 | 
| Ultra Press Inc.             |        2 | 
| Mountain Publication         |        2 | 
| Summer Night Publication     |        2 | 
| Pieterson Grp. of Publishers |        2 | 
| Novel Publisher Ltd.         |        2 | 
+------------------------------+----------+
8 rows in set (0.00 sec)

Previous: BIT_XOR()
Next: COUNT() with group by