GROUP BY CLAUSE:
The GROUP BY clause for summarizing the content of columns. The GROUP BY clause is used with group function to retrieve data group to one or more columns.
Its contain all the columns in the select clause expect those along with the GROUP function.
consider the table EMP
EMPNO ENAME DETPNO SALARY
101 amit 10 40000
102 sumit 10 25000
103 jatin 20 30000
104 lalit 20 25000
105 smita 30000
Ex: 1) Write a SQL query to display total amount of salary of each department.
Select deptno, sum(salary) from emp
group by deptno;
2) Write a SQL query to display total amount of salary department 10.
Select deptno, sum(salary) from emp
group by deptno;
2) Write a SQL query to display total amount of salary department 10.
Select deptno, sum(salary) from emp
Where depnto =10
group by deptno;
Ex: 3) Write a SQL query to display total amount of salary of each department excluding dept no 10
Select deptno, sum(salary) from emp
Where depnto <> 0
group by deptno;
HAVING CLAUSE :
The HAVING clause is used to include only certain groups produced by the GROUP BY clause in the query result set. It is equivalent to WHERE clause and used to specify the search criteria or search CONDITION WHERE group by clause is specified.
Ex: 4) Write a SQL query to display department no and total salary of employees of that particular department
Select count(empno),deptno
from emp
group by deptno
having count(empno)>3;
Where depnto =10
group by deptno;
Ex: 3) Write a SQL query to display total amount of salary of each department excluding dept no 10
Select deptno, sum(salary) from emp
Where depnto <> 0
group by deptno;
HAVING CLAUSE :
The HAVING clause is used to include only certain groups produced by the GROUP BY clause in the query result set. It is equivalent to WHERE clause and used to specify the search criteria or search CONDITION WHERE group by clause is specified.
Ex: 4) Write a SQL query to display department no and total salary of employees of that particular department
Select count(empno),deptno
from emp
group by deptno
having count(empno)>3;
Hi. I’m Designer of Blog Magic. I’m CEO/Founder of ThemeXpose. I’m Creative Art Director, Web Designer, UI/UX Designer, Interaction Designer, Industrial Designer, Web Developer, Business Enthusiast, StartUp Enthusiast, Speaker, Writer and Photographer. Inspired to make things looks better.
0 comments:
Post a Comment