SQL COUNT Function – Syntax and Examples

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:

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:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

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:

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

---

Examples: Using COUNT Function in Queries

Now, let’s explore different scenarios of using the COUNT function with the students table.

---

1. Counting All Rows in a Table

To count the total number of students in the students table:

SELECT COUNT(*) AS total_students
FROM students;

This query returns the total number of rows in the students table, regardless of NULL values.

---

2. Counting Non-NULL Values in a Column

To count the number of students who have a locality specified:

SELECT COUNT(locality) AS students_with_locality
FROM students;

This query returns the count of rows where locality is not NULL.

---

3. Counting Rows with a WHERE Clause

To count the number of students in the 9th grade:

SELECT COUNT(*) AS ninth_grade_students
FROM students
WHERE grade = '9th';

This query returns the count of students in the 9th grade only.

---

4. Using COUNT with GROUP BY

To count the number of students in each grade:

SELECT grade, COUNT(*) AS students_per_grade
FROM students
GROUP BY grade;

This query groups students by grade and returns the number of students in each grade.

---

FAQs for SQL COUNT

1. What does the SQL COUNT function do?

The COUNT function counts the number of rows or non-NULL values in a specified column in the dataset.

2. How is COUNT(*) different from COUNT(column_name)?

COUNT(*) counts all rows, including those with NULL values, while COUNT(column_name) counts only non-NULL values in the specified column.

3. Can I use COUNT with GROUP BY?

Yes, using COUNT with GROUP BY allows you to count rows for each group within a specified column.

4. Can COUNT be used with WHERE conditions?

Yes, you can filter rows before counting by using a WHERE clause with the COUNT function.

5. Does COUNT include NULL values?

COUNT(*) includes all rows, while COUNT(column_name) excludes NULL values from the count.