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

No comments:

Post a Comment