How to use COUNT and HAVING Clause Example In SQL
Consider a table STUDENT having the following schema:
STUDENT (Student_id, Student_Name, Address, Marks)
Student_id is the primary column of STUDENT table. Let first create the table structure with CREATE Table Command in SQL:
CREATE TABLE STUDENT
(STUDENT_ID NUMBER (4),
STUDENT_NAME VARCHAR2 (20),
ADDRESS VARCHAR2 (20),
MARKS NUMBER (3),
PRIMARY KEY (STUDENT_ID));
Now, insert values into the table using INSERT INTO Command in SQL:
INSERT INTO STUDENT
VALUES (100, ‘PUJA’, ’NOIDA’, 10);
INSERT INTO STUDENT
VALUES (101, ‘SUDO’, ’PUNE’, 30);
INSERT INTO STUDENT
VALUES (102, ‘BHALU’, ’NASHIK’, 40);
INSERT INTO STUDENT
VALUES (103, ‘CHETENA’, ’NOIDA’, 20);
INSERT INTO STUDENT
VALUES (104, ‘MOMO’, ’NOIDA’, 40);
Now display the content of STUDENT table:
SELECT *
FROM STUDENT;
Student_id Student_Name Address Marks
------------------------------------------------
100 PUJA NOIDA 10
101 SUDO PUNE 30
102 BHALU NASHIK 40
103 CHETENA NOIDA 20
104 MOMO NOIDA 40
Example 1
Print the marks and the number of students having marks higher than the average marks of students from NOIDA city.
Explanation: To get the average marks of students from NOIDA City, we use this query:
SELECT AVG(MARKS)
FROM STUDENT
WHERE ADDRESS =’NOIDA’
SELECT MARKS, COUNT (DISTINCT STUDENT_ID)
FROM STUDENT
GROUP BY MARKS
HAVING MARKS > (SELECT AVG(MARKS)
FROM STUDENT
WHERE ADDRESS = ’NOIDA’ );
In the above query, we use GROUP BY MARKS, which means it clusters the rows with same marks, and we also use SELECT MARKS, COUNT(DISTINCT STUDENT_ID) which prints the Marks of each cluster and the count of rows of respective clusters, i.e.,
MARKS COUNT
10 1
20 1
30 1
40 2
After that, we use HAVING MARKS > (SELECT AVG(MARKS) FROM STUDENT WHERE ADDRESS =’NOIDA’), which is used to filter the result with condition that marks must be greater than the avg marks of student from Noida city, i.e., more than
(10+20+40) / 3
= 23.3
Output:
MARKS COUNT (DISTINCT STUDENT_ID)
30 1
40 2
Example 2
Display the Names and Addresses of the students whose name’s second letter is U.
Explanation: For matching the pattern of the STUDENT_NAME field we used LIKE string comparison operator with two reserved character % and _ . % replaces an arbitrary number of characters, and ‘_’ replaces a single arbitrary character. Here, we need to compare the second letter of STUDENT_NAME thus we use the pattern ‘_U%’.
SELECT Student_Name, Address
FROM STUDENT
WHERE STUDENT_NAME LIKE ‘_U%’
Output:
STUDENT_NAME ADDRESS
PUJA NOIDA
SUDO PUNE
Example 3:
Print the details of the student obtaining the highest marks (if there is more than one student getting the highest marks, then the highest will be according to the alphabetical order of their names).
field,Explanation: To get the highest marks from the MARKS field we use the MAX command i.e.,
SELECT MAX(MARKS)
FROM STUDENT;
We use the above sub-query, which returns ‘40’ and it will be used with WHERE command. To arrange according to alphabetical order of STUDENT_NAME field, we used ORDER BY clause and for getting the top row, LIMIT 1 will be used. Combining all these:
SELECT * FROM STUDENT
WHERE MARKS = (SELECT MAX (MARKS)
FROM STUDENT)
ORDER BY STUDENT_NAME LIMIT 1;
Output:
Student_id Student_Name Address Marks
102 BHALU NASHIK 40
Example 4
Change the name and address of the student with ID 103 to RITA and DELHI, respectively.
Explanation: To change the value of any attributes we will use UPDATE command with SET clause to specify their new values.
UPDATE STUDENT
SET STUDENT_NAME = ’RITA’, ADDRESS=’DELHI’
WHERE STUDENT_ID=103 ;
Output:
1 row updated
To see the changes we will use,
SELECT * FROM STUDENT;
Output:
Student_id Student_Name Address Marks
100 PUJA NOIDA 10
101 SUDO PUNE 30
102 BHALU NASHIK 40
103 RITA DELHI 20
104 MOMO NOIDA 40
Example 5
DELETE the details from the STUDENT table those are getting lowest mark. Explanation: To find the lowest mark we will use,
SELECT MIN(MARKS)
FROM STUDENT;
It will return ‘10’ as a lowest marks. To delete rows we will use DELETE command with WHERE command for specify the condition.
DELETE FROM STUDENT
WHERE MARKS = (SELECT MIN(MARKS)
FROM STUDENT);
Output:
1 row affected
To see the changes we will use,
SELECT * FROM STUDENT;
Output:
Student_id Student_Name Address Marks
101 SUDO PUNE 30
102 BHALU NASHIK 40
103 RITA DELHI 20
104 MOMO NOIDA 40
How to Use GROUP BY and HAVING in SQL?
The COUNT and HAVING clauses together is used to count the number of rows in each group and then filter the result set based on those counts.
Here, we will discuss the SQL query using the COUNT and HAVING clauses in one single statement.