Learning of SQL Day 16

 

Day 16: GROUP BY Clause

Introduction:

• The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used with aggregate functions to perform calculations on each group of rows.

Key Concepts:

• Grouping Rows: Group rows based on one or more columns.

• Aggregate Functions with GROUP BY: Perform calculations on each group.

• HAVING Clause: Filter groups based on conditions.

SQL Commands and Examples:

1. Basic GROUP BY Clause:

   • Group rows by a specified column and use an aggregate function.

sql

-- Group students by age and count the number of students in each age group
SELECT Age, COUNT(*) AS NumberOfStudents
FROM Students
GROUP BY Age;

2. Grouping by Multiple Columns:

   • Group rows by multiple columns.

sql

-- Group employees by department and job title, and count the number of employees in each group
SELECT Department, JobTitle, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY Department, JobTitle;

3. Using HAVING Clause:

   • Filter groups based on conditions.

sql

-- Group courses by credits and count the number of courses with more than 3 credits
SELECT Credits, COUNT(*) AS NumberOfCourses
FROM Courses
GROUP BY Credits
HAVING COUNT(*) > 3;

Practice Exercise:

1. Use the Employees table with columns EmployeeID, FirstName, LastName, Department, Salary.

2. Write a query to group employees by Department and calculate the average Salary for each department.

3. Write a query to group employees by Department and count the number of employees in each department.

4. Write a query to group students by Age and find the maximum age in each group.

5. Write a query to group courses by Credits and use the HAVING clause to show only groups with more than 2 courses.

sql

-- Group employees by department and calculate the average salary for each department
SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department;

-- Group employees by department and count the number of employees in each department
SELECT Department, COUNT(*) AS NumberOfEmployees
FROM Employees
GROUP BY Department;

-- Group students by age and find the maximum age in each group
SELECT Age, MAX(Age) AS MaxAge
FROM Students
GROUP BY Age;

-- Group courses by credits and show only groups with more than 2 courses
SELECT Credits, COUNT(*) AS NumberOfCourses
FROM Courses
GROUP BY Credits
HAVING COUNT(*) > 2;

Using the GROUP BY clause effectively allows you to perform aggregate calculations on grouped data, making it easier to analyze and understand your data.