MySQL COUNT() function
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
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>
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>
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>
Online Practice Editor:
Previous:
BIT_XOR()
Next:
COUNT() with group by
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises