Php mysql count rows where condition

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

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

Counting how many authors are there in the authors table:

query('SELECT COUNT(*) FROM author') as $row) {
echo "";
echo "";
echo "";
}
?>


Number of authors
" . $row['COUNT(*)'] . "

View the example in browser

JSP Script

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>







<%
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);
%>

<%
while (rs.next()) {
%>

<%   }    %>


Number of authors

<%=rs.getString("COUNT(*)")%>

<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>

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

Taking an account of how many authors are there in different cities of USA and UK:

query('SELECT country,pub_city,COUNT(*)
FROM publisher WHERE country="USA" OR country="UK" GROUP BY country,pub_city;') as $row) {
echo "";
echo "";
echo "";
echo "";
echo "";
}
?>


Country
City
Number of authors
" . $row['country'] . "
" . $row['pub_city'] . "
" . $row['COUNT(*)'] . "

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

Displaying the name of the publisher and their frequency in publisher table, whose publisher id present in both publisher and book_mast tables:

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 "";
echo "";
echo "";
echo ""; 
}
?>


Publisher
Frequency
" . $row['pub_name'] . "
" . $row['COUNT(*)'] . "

View the example in browser

Online Practice Editor:

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