How to Use WHERE , GROUP BY , HAVING AND ORDER BY clauses together?

>>>Privious>>>

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

Using WHERE , GROUP BY , HAVING AND ORDER BY clauses together.

General form of the SELECT statement is

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

when all the above said clauses are used in a select query they need to appear in the order specified above, violation of which will lead to Errors.

Q. A query to display departments paid 500 or more and have 2 or less employess can be displayed as shown below

SQL >SELECT DEPTNO ,COUNT(*) , SUM(SAL) FROM EMP
2 WHERE JOB = 'CLERK'
3 GROUP BY DEPTNO
4 HAVING SUM(SAL) >=500 AND COUNT(*) <>

DEPTNO COUNT(*) SUM(SAL)
---------- ---------- ----------
30 1 950
10 1 1300

>>>Next>>>