MySQL COUNT() function

Last update on August 19 2022 21:50:42 (UTC/GMT +8 hours)

COUNT() function

MySQL COUNT() function returns a count of a number of non-NULL values of a given expression.

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

Syntax

COUNT(expr);

Where expr is an expression.

MySQL Version: 5.6

Contents:

Example : MySQL COUNT() function
MySQL COUNT() with logical operator and example
MySQL COUNT() using multiple tables and example

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;

Sample Output:

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

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-aggregate-functions-and-grouping-count-function- php MySQL examples | w3resource</title>
<meta name="description" content="example-aggregate-functions-and-grouping-count-function- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Counting how many authors are there in the authors table:</h2>
<table class='table table-bordered'>
<tr>
<th>Number of authors</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT COUNT(*) FROM author') as $row) {
echo "<tr>";
echo "<td>" . $row['COUNT(*)'] . "</td>";
echo "</tr>";
}
?>
</table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

JSP Script

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<%
try {
Class.forName("com.MySQL.jdbc.Driver").newInstance();
String Host = "jdbc:MySQL://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data = "SELECT COUNT(*) FROM author";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr  width="10" bgcolor="#9979">
<td>Number of authors</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("COUNT(*)")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

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;

Sample 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

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-aggregate-functions-and-grouping-count-with-logical-operator- php MySQL examples | w3resource</title>
<meta name="description" content="example-aggregate-functions-and-grouping-count-with-logical-operator- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Taking an account of how many authors are there in different cities of USA and UK:</h2>
<table class='table table-bordered'>
<tr>
<th>Country</th><th>City</th><th>Number of authors</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT country,pub_city,COUNT(*)
FROM publisher WHERE country="USA" OR country="UK" GROUP BY country,pub_city;') as $row) {
echo "<tr>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['pub_city'] . "</td>";
echo "<td>" . $row['COUNT(*)'] . "</td>";
echo "</tr>";
}
?>
</table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

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;

Sample 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)

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-aggregate-functions-and-grouping-count-with-more-tables- php MySQL examples | w3resource</title>
<meta name="description" content="example-aggregate-functions-and-grouping-count-with-more-tables- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Displaying the name of the publisher and their frequency in publisher table, whose publisher id present in both publisher and book_mast tables:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher</th><th>Frequency</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname"; 
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT publisher.pub_name,COUNT(*)
FROM publisher,book_mast
WHERE publisher.pub_id=book_mast.pub_id
GROUP BY publisher.pub_id') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['COUNT(*)'] . "</td>";
echo "</tr>"; 
}
?>
</table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Online Practice Editor:

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