SQL GROUP BY Clause
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
- 201 views