Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function


SQL GROUP BY Clause Statement


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.


SQL GROUP BY Clause Syntax

Considering following syntax that help you to understanding GROUP BY clause,

SELECT 
	column_name1, column_name2, aggregate_function(column_name), ....
	FROM table_name
    [ WHERE condition ]
    GROUP BY column_name1, ...;

SQL GROUP BY Clause Example Statement

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.

Run it...   »

Example Statement :

SQL> SELECT name 
	FROM employee_hour 
	GROUP BY name;

NAME
--------------------
Opal Kole
Beccaa Moss
Paul Singh
Max Miller

Run it...   »

GROUP By clause apply only single column that return group of employee name.

Example Statement :

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

Run it...   »

GROUP By clause apply on name column with use SUM aggregate function to summation the total working hours.

Example Statement :

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

Run it...   »

GROUP By clause apply on name column with use AVG aggregate function to average the total working hours divide by number of day.