Skip to main content

SQL GROUP BY Clause

Body

In the SQL SELECT Statement we can also group data upon certain conditions, the GROUP BY condition can only be applied if we have redundant values in at least one column upon which we can group things.

If every column of each row has unique value then the GROUP BY clause will not group any thing together and will not give proper results.

The GROUP BY clause also requires the use of an aggregate function, which does some mathematical calculations upon the grouped data.

The aggregate functions can be SUM(), COUNT(), AVG(), MAX(), MIN(), first(), last(), Mid(), etc.

The GROUP BY clause can be used in the SELECT statement as the following syntax

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name

Consider the following table

Dept:

Firstname Sal DeptNo
Mathew 2000 1
Bill 1500 1
Steve 2500 1
Henry 1100 2
Scott 1300 2
Philips 2000 3
SELECT DeptNo, AVG(sal) FROM Dept GROUP BY DeptNo.

Dept:

Deptno AVG(Sal)
1 2000
2 1200
3 2000

We can also group the data on more than one column as

SELECT column1, column2, sum(salary) FROM table GROUP BY column1,column2

The important thing to note with the GROUP BY clause is that we have to also select the items with which we group the data, we cannot skip the column name in the select part if we used it in the group clause.

Add new comment