SQL: Grouping Data with GROUP BY

Objectives:

• Learn how to use the GROUP BY clause to group rows that have the same values in specified columns
• Understand aggregate functions such as COUNT(), SUM(), AVG(), MIN(), and MAX()
• Practice writing queries with GROUP BY and aggregate functions

1. Introduction to GROUP BY Clause

Definition:

• The GROUP BY clause groups rows that have the same values in specified columns into summary rows.

Syntax Overview:

• Group rows by one or more columns.

  SELECT column1, aggregate_function(column2)
  FROM table_name
  GROUP BY column1;

Example:

SELECT CourseID, COUNT(StudentID) AS NumberOfStudents
FROM Enrollments
GROUP BY CourseID;

2. Using COUNT() Function

Syntax:

• COUNT() is used to count the number of rows in each group.

  SELECT column_name, COUNT(*)
  FROM table_name
  GROUP BY column_name;

Example:

SELECT CourseID, COUNT(*) AS NumberOfEnrollments
FROM Enrollments
GROUP BY CourseID;

Exercise:

• Write SQL queries to:

1. Count the number of students in each course.
2. Count the number of students enrolled in each course.

3. Using SUM() Function

Syntax:

• SUM() is used to calculate the total sum of a numeric column.

  SELECT column_name, SUM(column_name)
  FROM table_name
  GROUP BY column_name;

Example:

SELECT CourseID, SUM(Fee) AS TotalFees
FROM Enrollments
GROUP BY CourseID;

Exercise:

• Write SQL queries to:

1. Calculate the total fees for each course.
2. Sum the enrollment fees for all courses.

4. Using AVG() Function

Syntax:

• AVG() calculates the average value of a numeric column.

  SELECT column_name, AVG(column_name)
  FROM table_name
  GROUP BY column_name;

Example:

SELECT CourseID, AVG(Fee) AS AverageFee
FROM Enrollments
GROUP BY CourseID;

Exercise:

• Write SQL queries to:

1. Find the average fee for each course.
2. Calculate the average enrollment fee across all courses.

5. Using MIN() and MAX() Functions

Syntax:

• MIN() returns the minimum value of a numeric column.
• MAX() returns the maximum value of a numeric column.

  SELECT column_name, MIN(column_name), MAX(column_name)
  FROM table_name
  GROUP BY column_name;

Example:

SELECT CourseID, MIN(Fee) AS MinFee, MAX(Fee) AS MaxFee
FROM Enrollments
GROUP BY CourseID;

Exercise:

• Write SQL queries to:

1. Find the minimum and maximum fees for each course.
2. Get the minimum and maximum enrollment fees for all courses.

6. Practical Exercise: GROUP BY with Aggregate Functions

• Task:
• Use the Students, Courses, and Enrollments tables.
• Insert sample data into Students, Courses, and Enrollments tables:

  INSERT INTO Courses (CourseID, CourseName, Fee)
  VALUES 
      (1, 'Math', 500),
      (2, 'Science', 600),
      (3, 'History', 450);

  INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID, Fee)
  VALUES 
      (1, 1, 1, 500),
      (2, 2, 2, 600),
      (3, 3, 1, 450),
      (4, 4, 3, 400);

• Exercises:

1. List the number of students enrolled in each course using COUNT().
2. Calculate the total fee for each course using SUM().
3. Find the average fee for each course using AVG().
4. Determine the minimum and maximum fees for each course using MIN() and MAX().

Exercises

1. Using COUNT():

• Exercise: Write SQL queries to count the number of students in each course.

1. Using SUM():

• Exercise: Write SQL queries to calculate the total fee for each course.

1. Using AVG():

• Exercise: Write SQL queries to find the average fee for each course.

1. Using MIN() and MAX():

• Exercise: Write SQL queries to determine the minimum and maximum fees for each course.

Homework:

• Practice Questions:
• Write SQL queries to:
  • Count the number of students in each course.
  • Find the total fee for each course.
  • Calculate the average fee for each course.
  • Get the minimum and maximum fees for each course.