SQL COUNT WHERE

SQL Count()函数在SELECT语句中也可以与WHERE子句一起使用。

带有WHERE子句的Count()函数仅返回与WHERE子句中指定条件匹配的表中的行。

SQL Count()函数带有WHERE子句的语法

SELECT COUNT(Column_Name) FROM Table_Name WHERE [Condition];

SQL Count函数带有WHERE子句的示例

示例1： 首先创建一个表，然后运行count函数。

下面的CREATE TABLE语句创建了一个名为Cars_Details的表，包含五个字段：

CREATE TABLE Cars_Details
(
Car_Number INT PRIMARY KEY,
Car_Model INT,
Car_Name VARCHAR (50),
Car_Price INT NOT NULL,
Car_AmountINT NOT NULL
) ;

以下INSERT查询将汽车记录插入Cars_Details表：

INSERT INTO Cars_Details (Car_Number, Car_Model, Car_Name, Car_Amount, Car_Price) 
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);

以下SELECT查询显示了 Cars_Details 表的数据：

SELECT * FROM Cars_Details;

以下查询显示了车辆编号大于等于7000的车辆的总价值：

SELECT COUNT(Car_Name) As "Number_of_Cars"FROM Cars_Details WHERE Car_Number >= 7000;

输出：

示例2： 下面的查询使用CREATE TABLE语句创建 College_Student_Details 表：

CREATE TABLE College_Student_Details
(
Student_ID INT NOT NULL, 
Student_Name varchar(100),
Student_Course varchar(50),
Student_Age INT, 
Student_Marks INT
); 

以下SQL查询使用INSERT INTO语句将学生的记录插入上述表中：

INSERT INTO College_Student_Details VALUES (101, Anuj, B.tech, 20, 88);
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);

让我们使用以下SELECT语句来查看上表的记录：

SELECT * FROM College_Student_Details;

下面的计数查询显示了那些学生总数，其 学生分数 大于90：

SELECT COUNT(*) As "Number_of_Students"FROM Collge_Student_Details WHERE Student_Marks > 90;

结果：