SQL GROUP BY CLAUSE

Posted on Wednesday, 08 March 2023 10:34 AM

SQL GROUP BY is a clause in SQL that is used to group rows with the same values in one or more columns into summary rows, like computing an aggregate function on a set of rows with the same value in a particular column.

GROUP BY Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

For example,

Let's say we want to retrieve the total number of exams taken by each student. We can use the GROUP BY clause to group the rows by student ID and then count the number of exams for each group.

Here's an example query to accomplish this:

SELECT student_id, COUNT(*) as exam_count
FROM exam
GROUP BY student_id;

OUTPUT

In this example,

we first select the student_id column and the COUNT(*) function, which counts the number of rows in each group. We then specify the exam table and group the rows by the student_id column using the GROUP BY clause.

The result of this query will be a table that contains the student IDs and the number of exams taken by each student.

With aggregate functions

GROUP BY can also be used with other aggregate functions, such as SUM, AVG, MIN, and MAX, to compute summary statistics for each group. For example, let's say we want to retrieve the average marks scored by each student across all exams.

Here's an example query to accomplish this:

SELECT student_id, AVG(marks) as average_marks
FROM exam
GROUP BY student_id;

OUTPUT

In this example,

We use the AVG function to compute the average of the marks column for each group of rows with the same student ID. The result will be a table that contains the student IDs and the average marks scored by each student.

So, GROUP BY is a powerful tool in SQL for summarizing data by grouping rows based on one or more columns. It can be used with a variety of aggregate functions to compute summary statistics for each group, such as counts, sums, averages, and more.

K-Learn