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:

-- This SQL query counts the number of rows in the author table.
SELECT COUNT(*) -- Selects the count of all rows from the author table
FROM author; -- Specifies the table from which to retrieve data (author table)

Explanation:

• This SQL query retrieves data from the author table.


• It counts the total number of rows in the author table.


• Here's how the process works:


• The query uses the COUNT(*) function to count all rows in the specified table.


• Since * is used as an argument to COUNT, it counts all rows regardless of the values in any specific column.


• Finally, the query returns the count of rows from the author table.

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:

-- This SQL query counts the number of publishers in each city for the countries USA and UK.
SELECT country, pub_city, COUNT(*) -- Selects the country, publisher city, and counts the number of publishers in each city
FROM publisher -- Specifies the table from which to retrieve data (publisher table)
WHERE country='USA' OR country='UK' -- Filters the data to include only publishers from the countries USA and UK
GROUP BY country, pub_city; -- Groups the results by country and publisher city, so that the count is calculated for each combination of country and city

Explanation:

• This SQL query retrieves data from the publisher table.


• It counts the number of publishers in each city for the countries USA and UK.


• The WHERE clause filters the data to include only publishers from the countries USA and UK.


• The GROUP BY clause ensures that the results are grouped by country and publisher city, allowing for separate counts to be calculated for each combination of country and city.


• Here's how the process works:


• The query selects the country, publisher city, and counts the number of publishers in each city.


• It filters the data to include only publishers from the countries USA and UK using the WHERE clause.


• The results are then grouped by country and publisher city using GROUP BY country, pub_city, ensuring that the count is calculated for each combination of country and city.


• Finally, the query returns the country, publisher city, and the count of publishers for each combination of country and 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:

-- This SQL query counts the number of books published by each publisher and retrieves the publisher names.
SELECT publisher.pub_name, COUNT(*) -- Selects the publisher name and counts the number of books published by each publisher
FROM publisher, book_mast -- Specifies the tables from which to retrieve data (publisher and book_mast tables)
WHERE publisher.pub_id = book_mast.pub_id -- Specifies the condition to join the tables based on publisher ID
GROUP BY publisher.pub_id; -- Groups the results by publisher ID, so that the count is calculated for each publisher

Explanation:

• This SQL query retrieves data from the publisher and book_mast tables.


• It counts the number of books published by each publisher and retrieves the publisher names.


• The tables are joined based on the common column pub_id.


• The GROUP BY clause ensures that the results are grouped by publisher ID, allowing for separate counts to be calculated for each publisher.


• Here's how the process works:


• The query selects the publisher name (publisher.pub_name) and counts the number of books published by each publisher.


• It specifies the condition to join the publisher and book_mast tables based on the publisher ID (publisher.pub_id = book_mast.pub_id).


• The results are then grouped by publisher ID using GROUP BY publisher.pub_id, ensuring that the count is calculated for each publisher.


• Finally, the query returns the publisher name and the count of books published by each publisher.

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