SQL COUNT WHERE
The SQL Count() function can also be used with the WHERE clause in the SELECT statement.
The Count() with WHERE clause returns only those rows from the table which match with the condition specified in the WHERE clause.
Syntax of SQL Count() Function With WHERE clause
Examples of SQL Count Function with WHERE clause
Example 1: First create a table and then run the count function.
The following CREATE TABLE statement creates the Cars_Details table with five fields:
(
Car_Number INT PRIMARY KEY,
Car_Model INT,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
) ;
The following INSERT query inserts the record of cars into the Cars_Details table:
VALUES (2578, 2018, Creta, 3, 1500000),
(9258, 2019, Audi, 2, 3000000),
(8233, 2018, Venue, 6, 900000),
(8990, 2018, Nexon, 7, 700000),
(7085, 2020, Mercedes, 6, 8000000),
(1258, 2021, Thar, 2, 1500000),
(2564, 2019, Jaguar, 4, 6000000),
(9578, 2020, Scorpio, 8, 1800000);
The following SELECT query shows the data of the Cars_Details table:
| Car_Number | Car_Name | Car_Amount | Car_Price |
|---|---|---|---|
| 2578 | Creta | 3 | 900000 |
| 9258 | Audi | 2 | 1100000 |
| 8233 | Venue | 6 | 900000 |
| 8990 | Nexon | 7 | 700000 |
| 7085 | Mercedes | 6 | 8000000 |
| 1258 | Thar | 2 | 1500000 |
| 2564 | Jaguar | 4 | 6000000 |
| 9578 | Scorpio | 8 | 1800000 |
The following query shows the total value of those cars whose Car_Number is greater than and equal to 7000:
Output:
Example 2: The following query creates the College_Student_Details table using the CREATE TABLE statement:
(
Student_ID INT NOT NULL,
Student_Name varchar(100),
Student_Course varchar(50),
Student_Age INT,
Student_Marks INT
);
The following SQL queries insert the record of students into the above table using INSERT INTO statement:
INSERT INTO College_Student_Details VALUES (102, Raman, MCA, 24, 98);
INSERT INTO College_Student_Details VALUES (104, Shyam, BBA, 19, 92);
INSERT INTO College_Student_Details VALUES (107, Vikash, B.tech, 20, 78);
INSERT INTO College_Student_Details VALUES (111, Monu, MBA, 21, 65);
INSERT INTO College_Student_Details VALUES (114, Jones, B.tech, 18, 93);
INSERT INTO College_Student_Details VALUES (121, Parul, BCA, 20, 97);
INSERT INTO College_Student_Details VALUES (123, Divya, B.tech, 21, 89);
INSERT INTO College_Student_Details VALUES (128, Hemant, MBA, 23, 90);
INSERT INTO College_Student_Details VALUES (130, Nidhi, BBA, 20, 88);
INSERT INTO College_Student_Details VALUES (132, Priya, MBA, 22, 99);
INSERT INTO College_Student_Details VALUES (138, Mohit, MCA, 21, 92);
Let's see the record of the above table using the following SELECT statement:
| Student_ID | Student_Name | Student_Course | Student_Age | Student_Marks |
|---|---|---|---|---|
| 101 | Anuj | B.tech | 20 | 88 |
| 102 | Raman | MCA | 24 | 98 |
| 104 | Shyam | BBA | 19 | 92 |
| 107 | Vikash | B.tech | 20 | 78 |
| 111 | Monu | MBA | 21 | 65 |
| 114 | Jones | B.tech | 18 | 93 |
| 121 | Parul | BCA | 20 | 97 |
| 123 | Divya | B.tech | 21 | 89 |
| 128 | Hemant | MBA | 23 | 90 |
| 130 | Nidhi | BBA | 20 | 88 |
| 132 | Priya | MBA | 22 | 99 |
| 138 | Mohit | MCA | 21 | 92 |
The following Count query displays the total number of those students whose Student_Marks is greater than 90:
Output:
