SQL Data Grouping and Filtering – GROUP BY and HAVING Tutorial

SQL Data Grouping and Filtering – GROUP BY and HAVING

SQL provides GROUP BY and HAVING clauses to group rows based on a column and filter groups according to a condition. These clauses are often used with Aggregate Functions like COUNT, SUM, AVG, MIN, and MAX.

1. GROUP BY

Definition: Groups rows that have the same values in specified columns into summary rows.

Syntax:

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

Example:

SELECT dept_id, COUNT(*) AS total_students
FROM Student
GROUP BY dept_id;

Groups students by department and counts the number of students in each department.

2. HAVING

Definition: Filters groups created by GROUP BY according to a specified condition. Unlike WHERE, HAVING works on aggregated data.

Syntax:

SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1
HAVING AGG_FUNC(column2) condition;

Example:

SELECT dept_id, COUNT(*) AS total_students
FROM Student
GROUP BY dept_id
HAVING COUNT(*) >= 10;

Displays only departments that have 10 or more students.

Lab Practice Tasks

  1. Group students by department and count them using GROUP BY.
  2. Find departments with more than 5 students using HAVING.
  3. Calculate total salary per department and filter departments with total salary > 50,000.

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যসমূহ (Atom)