MySQL COUNT() Function

  1. Overview
  2. MYSQL
  3. Numeric Functions
  4. MySQL COUNT() Function

The COUNT() function in MySQL is used to return the number of records (rows) in a specified column that are not NULL. It is often used to count the number of rows returned by a query or the number of occurrences of a specific value in a column.

Syntax

COUNT(expression)

Definition and Usage

  • The COUNT() function counts the number of rows that match a specified condition.
  • Important: It does not count NULL values in the specified expression.

Parameter Values

Parameter Description
expression Required. A field or a string value to count. If this is a column, it will count all non-NULL values in that column. If a constant string is provided, it will count all rows regardless of their value.

Technical Details

  • Works in: From MySQL 4.0
  • Result: Returns the count as an integer value, representing the number of non-NULL records.

Examples

Example 1: Count the number of products

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;

Result:

NumberOfProducts: 150

Explanation: This query returns the count of all non-NULL values in the ProductID column in the Products table.

Example 2: Count the total number of rows in a table

SELECT COUNT(*) AS TotalRows FROM Products;

Result:

TotalRows: 200

Explanation: This query counts all rows in the Products table, including those with NULL values in any column.

Example 3: Count records with a specific condition

SELECT COUNT(*) AS ProductsInStock FROM Products WHERE StockQuantity > 0;

Result:

ProductsInStock: 120

Explanation: This query counts the number of products where the StockQuantity is greater than 0.

Usage Notes

  1. COUNT(*) vs COUNT(expression):

    • COUNT(*): Counts all rows in the table, including rows with NULL values in any column.
    • COUNT(expression): Counts non-NULL values in the specified column.

  2. NULL Values:

    • NULL values are excluded from the count when specifying a column (e.g., COUNT(ProductID)).

  3. Empty Table:

    • If the table has no rows, COUNT() returns 0.

Practical Query Example:

Example: Count the number of distinct values in a column

SELECT COUNT(DISTINCT Category) AS UniqueCategories FROM Products;

Result:

UniqueCategories: 10

Explanation: This query counts the number of distinct categories in the Products table.

The COUNT() function is a very useful aggregate function in SQL for determining the number of records that meet a certain criterion or simply counting all records in a table.

Was this article helpful?