SQL Constructs : HAVING

The Having clause is used with SQL Queries to give more precise condition for a statement. It is used to mention condition in Group based SQL functions, just like WHERE clause. it enables the user to specify conditions that filter which group results appear in the results. The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause. The Basic syntax of HAVING clause is as follow :
 SELECT column_name, function(column_name) 
 FROM table_name 
 WHERE column_name [condition] 
 GROUP BY column_name 
 HAVING function(column_name) condition;
For example consider the below table students :
 STUDENT_ID  STUDENT_NAME    MARKS     GRADE
 ---------- --------------  --------- -------
 101       John               89         A    
 102       Bruce              85         A    
 103       Kevin              91         A    
 104       Rick               37         C    
 105       Joe                95         A    
 106       Patrik             57         B    
 107       Alice              50         B    
 108       Peter              87         A    
Now suppose we want to count the total number of students which are having A grade, then we can do this by the below query
 SQL> SELECT grade, COUNT(student_name) FROM students GROUP BY grade HAVING grade = 'A';
And result would be :
 GRADE   COUNT(STUDENT_NAME)
 -----  ------------------
  A       5



Next Topic :

No comments:

Post a Comment