SQL GROUP BY
SQL GROUP BY clause use with SELECT statement for fetching data (result groups) according to a matching values for one or more columns.
SELECT statement fetching all of the rows together that have specific data in specific columns for applying GROUP BY clause on one or more columns.
Consider following syntax that help you to understanding GROUP BY clause.
Syntax
SELECT
column_name1, column_name2, aggregate_function(column_name), ....
FROM table_name
[ WHERE condition ]
GROUP BY column_name1, ...;
Example
We have following employee_hour table that store weekday hours for each employee.
SQL> SELECT * FROM employee_hour;
NAME DAY HOURS
-------------------- ---------- ----------
Opal Kole Monday 8
Max Miller Monday 8
Beccaa Moss Monday 8
Paul Singh Monday 9
Opal Kole Tuesday 9
Max Miller Tuesday 6
Beccaa Moss Tuesday 10
Paul Singh Tuesday 8
Opal Kole Wednesday 7
Max Miller Wednesday 9
Beccaa Moss Wednesday 11
Paul Singh Wednesday 12
12 rows selected.
Example
SQL> SELECT name
FROM employee_hour
GROUP BY name;
NAME
--------------------
Opal Kole
Beccaa Moss
Paul Singh
Max Miller
GROUP By clause apply only single column that return group of employee name.
Example
SQL> SELECT name,SUM(hours)
FROM employee_hour
GROUP BY name;
NAME SUM(HOURS)
-------------------- ----------
Opal Kole 24
Beccaa Moss 29
Paul Singh 29
Max Miller 23
GROUP By clause apply on name column with use SUM aggregate function to summation the total working hours.
Example
SQL> SELECT name,AVG(hours)
FROM employee_hour
GROUP BY name;
NAME AVG(HOURS)
-------------------- ----------
Opal Kole 8
Beccaa Moss 9.66666667
Paul Singh 9.66666667
Max Miller 7.66666667
GROUP By clause apply on name column with use AVG aggregate function to average the total working hours divide by number of day.