SQL Day 2: Sorting, Aggregations, and Grouping Data

📌 Introduction

Today, I explored how to sort data, aggregate values, group data, and use filtering conditions like WHERE and HAVING. Understanding these concepts is essential for analyzing and organizing data effectively.

📝 What I Learned Today

1️⃣ Sorting Data: ORDER BY Clause

Sorting data is crucial when retrieving results in a meaningful order.

🔹 Ascending Order (Default)

By default, ORDER BY sorts data in ascending order (A-Z, 0-9).

SELECT name, age FROM students ORDER BY age;

🔹 Descending Order

To sort in descending order (Z-A, 9-0), we use DESC.

SELECT name, age FROM students ORDER BY age DESC;

2️⃣ Aggregation Functions: COUNT, SUM, AVG, MIN, MAX

Aggregation functions help in performing calculations on multiple rows.

Function Description Example
COUNT() Counts the number of rows SELECT COUNT(*) FROM students;
SUM() Adds up numeric values SELECT SUM(marks) FROM students;
AVG() Finds the average value SELECT AVG(salary) FROM employees;
MIN() Finds the smallest value SELECT MIN(price) FROM products;
MAX() Finds the largest value SELECT MAX(salary) FROM employees;

3️⃣ Grouping Data: GROUP BY Clause

GROUP BY is used to group rows that have the same values in specified columns.

🔹 Example: Counting Students in Each Grade 

SELECT grade, COUNT(*) FROM students GROUP BY grade;

This groups students based on their grade and counts how many are in each grade.

4️⃣ Filtering Data: WHERE vs. HAVING

Both WHERE and HAVING are used to filter results, but they work differently:

Clause Used for Works with Aggregation?
WHERE Filters individual rows before grouping ❌ No
HAVING Filters groups after aggregation ✅ Yes

🔹 Example: Filtering Before Grouping (WHERE) 

SELECT * FROM students WHERE age > 18;

🔹 Example: Filtering After Aggregation (HAVING) 

SELECT grade, COUNT(*) FROM students GROUP BY grade HAVING COUNT(*) > 5;

This shows only grades where more than 5 students exist.

5️⃣ Errors in GROUP BY and HAVING

Errors can occur if we misuse GROUP BY and HAVING.

🔴 Common Errors & Solutions

Error Cause Fix
Column not in GROUP BY Selecting a column that’s not in GROUP BY Use aggregation (SUM(), COUNT()) or include column in GROUP BY
HAVING without GROUP BY Using HAVING without an aggregation Use WHERE instead
WHERE with Aggregation Using WHERE with functions like COUNT() Use HAVING instead

🔹 Example of an Error 

SELECT grade, COUNT(*) FROM students WHERE COUNT(*) > 5 GROUP BY grade;

🔴 Error: COUNT(*) cannot be used with WHERE. ✅ Fix: Use HAVING instead:

SELECT grade, COUNT(*) FROM students GROUP BY grade HAVING COUNT(*) > 5;

🎯 Key Takeaways

  • Sorting: ORDER BY column ASC | DESC
  • Aggregations: COUNT(), SUM(), AVG(), MIN(), MAX()
  • Grouping: GROUP BY column_name
  • Filtering:
    • WHERE filters individual rows before grouping.
    • HAVING filters after aggregation.
  • Errors in GROUP BY & HAVING arise when misusing columns or filtering incorrectly.

💡 Final Thoughts

Today’s session gave me a deeper understanding of sorting, aggregating, and filtering data in SQL. Tomorrow, I plan to explore JOINS, subqueries, and indexing to improve query efficiency.

Stay tuned for Day 3! 🚀✨