SQL Constructs : GROUP BY

Group by clause is used to group the results of a SELECT query based on one or more columns. It is mainly used in collaboration with the SELECT statement to arrange identical data into groups. he GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. The Basic syntax of GROUP BY clause is as follow :
 SELECT column_name, function(column_name) FROM table_name GROUP BY column_name;
And the syntax of GROUP BY with in the WHERE clause :
 SELECT column_name, function(column_name) FROM table_name WHERE [condition] GROUP BY column_name;
For example consider the below table employee :
  ENAME    HIREDATE       SAL      PIN                         
 -------- -----------  --------  -----------   
  Priya    23-DEC-17      16000    978910       
  Nidhi    23-DEC-17      14000    123454       
  Dinesh   21-DEC-17      16000    768754       
  AJAY     21-DEC-17      18500    207521       
  Sumit    24-DEC-17      12500    879065      
  Deepak   24-DEC-17      14000    879065     
Now at the above table we can find how many employee have sal salary by the below query
 SQL > SCLECT sal, COUNT(ename) FROM employee GROUP BY sal;  
And result would be :
   SAL    COUNT(ENAME)
 -------  ------------
  14000     2
  18500     1
  12500     1
  16000     2 
we can also use WHERE clause to filter the result, for example :
 SQL > SELECT sal, COUNT(ename)FROM employee WHERE sal > 15000 GROUP BY sal;
The above query will first filter the employee which salary is less then 15000 then gorup them, so the result would be :
  SAL     COUNT(ENAME)
 -------  --------------
  18500     1
  16000     2



Next Topic :

No comments:

Post a Comment