TechCodeview

Funkcija SQL Count() također se može koristiti s klauzulom WHERE u izjavi SELECT.

Count() s klauzulom WHERE vraća samo one retke iz tablice koji odgovaraju uvjetu navedenom u klauzuli WHERE.

Sintaksa SQL Count() funkcije s WHERE klauzulom

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

Primjeri SQL funkcije brojanja s klauzulom WHERE

Primjer 1: Prvo napravite tablicu, a zatim pokrenite funkciju brojanja.

Sljedeća izjava CREATE TABLE stvara tablicu Cars_Details s pet polja:

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

Sljedeći upit INSERT umeće zapis automobila u tablicu 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); 

Sljedeći SELECT upit prikazuje podatke za the Cars_Details stol:

 SELECT * FROM Cars_Details; 

Sljedeći upit pokazuje ukupnu vrijednost onih automobila čiji je Car_Number veći od i jednak 7000:

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

Izlaz:

Primjer 2: Sljedeći upit stvara Fakultet_Student_Details tablica pomoću naredbe CREATE TABLE:

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

Sljedeći SQL upiti umeću zapis učenika u gornju tablicu pomoću naredbe 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); 

Pogledajmo zapis gornje tablice pomoću sljedeće SELECT naredbe:

 SELECT * FROM College_Student_Details; 

Sljedeći upit za brojanje prikazuje ukupan broj onih učenika čiji Ocjene_učenika je veći od 90:

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

Izlaz: