TechCodeview

Funkcijo SQL Count() lahko uporabite tudi s klavzulo WHERE v stavku SELECT.

Count() s klavzulo WHERE vrne samo tiste vrstice iz tabele, ki se ujemajo s pogojem, podanim v klavzuli WHERE.

Sintaksa funkcije SQL Count() s klavzulo WHERE

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

Primeri funkcije štetja SQL s stavkom WHERE

Primer 1: Najprej ustvarite tabelo in nato zaženite funkcijo štetja.

Naslednji stavek CREATE TABLE ustvari tabelo Cars_Details s petimi polji:

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

Naslednja poizvedba INSERT vstavi zapis avtomobilov v tabelo 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); 

Naslednja poizvedba SELECT prikazuje podatke za avtomobili_podrobnosti tabela:

 SELECT * FROM Cars_Details; 

Naslednja poizvedba prikazuje skupno vrednost tistih avtomobilov, katerih Car_Number je večja in enaka 7000:

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

Izhod:

Primer 2: Naslednja poizvedba ustvari College_Student_Details tabelo z uporabo stavka 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 ); 

Naslednje poizvedbe SQL vstavijo zapis študentov v zgornjo tabelo z uporabo izjave 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); 

Oglejmo si zapis zgornje tabele z naslednjim stavkom SELECT:

 SELECT * FROM College_Student_Details; 

Naslednja poizvedba za štetje prikazuje skupno število študentov, katerih Študentske_ocene je večji od 90:

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

Izhod: