Exforsys.com
 
Home Tutorials Oracle 9i
 

Tutorial 4: SQL functions

 

Tutorial 4: SQL functions - Page 6

Page 6 of 7



GROUP BY CLAUSE :   Using a group function one can  group  entire table data and provide a single result,however
you can use the GROUP BY clause to break the result set into logical groupings and get a result for each group.


 


Q. To display no of employees in each dept we can use.


SQL >SELECT DEPTNO,COUNT(*) FROM EMP   GROUP BY DEPTNO;


    DEPTNO   COUNT(*)
---------- ----------
        10          3
        20          5
        30          6


Q. The total salary paid for each department and also when the last recruitment was made in that department.


SQL >SELECT DEPTNO,MAX(HIREDATE),SUM(SAL) FROM EMP    GROUP BY DEPTNO;


    DEPTNO MAX(HIRED   SUM(SAL)
---------- --------- ----------
        10 23-JAN-82       8750
        20 23-MAY-87      10875
        30 03-DEC-81       9400


When grouping using the GROUP BY clause you cannot use a WHERE clause to restrict the groups
that are returned, SQL provides another selection keyword, HAVING, to replace the WHERE clause



Q,To diplay those departments which have more than 5 employees in them we can write.
  SQL >SELECT DEPTNO, COUNT(*) FROM EMP
          GROUP BY DEPTNO
          HAVING COUNT(*) > 5 ;


    DEPTNO   COUNT(*)
---------- ----------
        30          6




Next Page: Tutorial 4: SQL functions - Page 7


Read Next: Tutorial 5: Working with Multiple Tables



 

 

Comments



Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2009 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape