SQL COUNT Function

The SQL COUNT function is used to count the number of rows that match a specified condition or to count the total number of non-NULL values in a specified column. It’s particularly useful for getting the count of rows in a table or the number of rows that meet certain criteria.

The COUNT function can be used alone or with other SQL clauses such as GROUP BY and HAVING.

In this tutorial, we will go through SQL COUNT Function, its syntax, and how to use this function in SQL statements, with the help of well detailed examples.

Syntax of SQL COUNT Function

The basic syntax of the SQL COUNT function is as follows:

</>
Copy
SELECT COUNT(column_name) AS count_name
FROM table_name
[WHERE condition];

You can also use COUNT(*) to count all rows, including rows with NULL values.

  • SELECT: Specifies the columns to retrieve, in this case using COUNT to count rows.
  • COUNT(column_name): Returns the count of non-NULL values in the specified column.
  • COUNT(*): Returns the total count of rows, including those with NULL values.
  • FROM: Specifies the table from which to retrieve data.
  • WHERE (optional): Filters the data before applying the COUNT function.

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the COUNT function in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality along with a grades table that records students’ scores.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Insert sample data into the students and grades tables:

</>
Copy
INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');