Group By Function in SQL

Group By Function

  • It is used to group the data based on the column.
  • Always written after from clause.
  • All columns in the SELECT list that are not in group functions must be in the GROUP BY clause

GROUP BY Clause Syntax

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

Note:All columns in the SELECT list that are not in group functions must be in the GROUP BY clause.

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

The GROUP BY column does not have to be in the SELECT list

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;

Queries using Group By

1.Display number of employee working in each department?

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

2.Display job wise highest salary ?

SELECT job_id ,MAX(Salary) FROM employees GROUP BY Job_id;

3.Display department wise average salary ?

SELECT department_id ,AVG(Salary) FROM employees GROUP BY department_id;

4.job wise average salary and max salary?

SELECT department_id ,AVG(Salary),MAX(salary) FROM employees GROUP BY department_id;

Restricting Group Results with the HAVING Clause

  • It is used to filter the group data
  • It should written after group by clause.

Query:Display job wise highest salary only if the salary is greater than twelve thousands

SELECT job_id ,MAX(Salary)
FROM employees
GROUP BY Job_id
HAVING MAX(salary)>12000;

Note:
–You can not use the WHERE clause to restrict groups.
–You use the HAVING clause to restrict groups.
–You can not use group functions in the WHERE clause

Leave a Reply

Your email address will not be published. Required fields are marked *