How to use Count() in SQL

SQL (Structured Query Language) provides a range of mathematical functions that can be used to summarize data efficiently.

The COUNT() function returns the number of rows that match a requirement specified in the query.

Syntax

SELECT COUNT(columnName)
FROM tableName
WHERE condition;

Code

Let’s create a demo table called Student.

CREATE TABLE Student (
    s_ID int,
    s_name varchar(255),
    s_major varchar(255),
    s_age int
);
INSERT INTO Student
VALUES (78391,'Brad','CS',20);
INSERT INTO Student
VALUES (93742,'Allie','Econ',17);
INSERT INTO Student
VALUES (31289,'Sally','CS',18);
INSERT INTO Student
VALUES (21328,'Chris','Psych', 20);
INSERT INTO Student
VALUES (32891,'Betty','Arch', 19);

Demo table Student

s_ID

s_name

s_major

s_age

78391

Brad

CS

20

93742

Allie

Econ

17

31289

Sally

CS

18

21328

Chris

Psych

20

32891

Betty

Arch

19

Explanation

We can use the COUNT() function to get the total number of students without going through the entire database ourselves.

This returns the total number of rows in the Student table.

SELECT COUNT(*) As "Number of Students"
FROM Student;

COUNT() with WHERE clause

The WHERE clause is added with the function to get rows that fulfill a specific condition.

For example, if we wish to see the number of students above 18, we use the COUNT() function and specify the condition as above 18 in the WHERE clause.

SELECT COUNT(*) As "Students above 18"
FROM Student
WHERE s_age > 18;

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)