SQL Tutorial

Grouping in SQL

Grouping in SQL is used to arrange identical data into groups. It is typically used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to perform operations on each group of data.

📊 Why Grouping is Important

Grouping allows you to break down your dataset into meaningful categories. It's especially useful in data analysis where you need to summarize or aggregate data based on certain criteria.

💡 Common Grouping Techniques

• GROUP BY – Used to group rows based on a column's values
• HAVING – Used to filter groups (similar to WHERE, but for groups)
• ORDER BY – Used to sort the groups based on aggregate results

🔍 Syntax for Grouping

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

⚙️ Example with GROUP BY

SELECT department, COUNT(*) FROM employees GROUP BY department;

SELECT department, AVG(salary) FROM employees GROUP BY department;

⚠️ Using HAVING with GROUP BY

The HAVING clause is used to filter groups after the GROUP BY operation. It's often used in conjunction with aggregate functions to filter aggregated results.

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

🎯 Sorting Groups with ORDER BY

The ORDER BY clause can be used to sort the grouped data. It's often useful when you want to see the results in ascending or descending order of aggregate values.

SELECT department, AVG(salary)
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC;

💡 Common Pitfalls to Avoid

• Missing GROUP BY clause: If you use an aggregate function, always group by the non-aggregated columns!
• Using SELECT *: Avoid selecting all columns with SELECT * when using GROUP BY. Specify only the necessary columns.

🔑 Key Takeaways

• GROUP BY groups data into subsets based on column values.
• Use HAVING to filter groups based on aggregate functions.
• Use ORDER BY to sort the results of your grouped data.