SQL GROUP BY Statement

Aggregate functions often need an added GROUP BY statement.

The GROUP BY Statement

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SQL GROUP BY Syntax

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

SQL GROUP BY Example

We have the following "result" table:

Student_No	Subject_No	Year_Exam	Marks
ST101	SU03	1	69
ST102	SU01	1	61
ST101	SU04	1	70
ST101	SU05	1	87
ST103	SU01	2	51
ST103	SU03	1	59
ST103	SU01	3	56
ST108	SU01	1	78
ST105	SU05	2	98

Now we want to find the total sum (total Marks) of each Student.

We will have to use the GROUP BY statement to group the Student.

We use the following SQL statement:

SELECT Student_No,SUM(Marks) AS Total_Marks FROM result GROUP BY Student_No;

The result-set will look like this:

Student_No	Total_Marks
ST101	226
ST102	61
ST103	166
ST105	98
ST108	78

Let's see what happens if we omit the GROUP BY statement:

SELECT Student_No,SUM(Marks) AS Total_Marks FROM result;

The result-set will look like this:

Student_No	Total_Marks
ST101	629

GROUP BY More Than One Column

We can also use the GROUP BY statement on more than one column, like this:

SELECT Student_No,Year_Exam,SUM(Marks) AS Total_Marks FROM result GROUP BY Student_No,Year_Exam;

The result-set will look like this:

Student_No	Year_Exam	Total_Marks
ST101	1	226
ST102	1	61
ST103	1	59
ST108	1	78
ST103	2	51
ST105	2	98
ST103	3	56