Aggregate Functions and Grouping Data in SQL

 Aggregate Functions and Grouping Data in SQL

So far, we’ve learned how to retrieve and filter data. But what if you want to summarize large datasets? For example, calculating total sales, average salaries, or counting employees in each department. That’s where aggregate functions and GROUP BY come in.

In this blog, we’ll break down these powerful tools with practical examples.

1. What Are Aggregate Functions?

Aggregate functions perform calculations on multiple rows and return a single value. Common ones include:

FunctionPurpose
COUNT()            Count rows
SUM()            Sum of values
AVG()            Average of values
MIN()            Minimum value
MAX()            Maximum value

Example: COUNT

SELECT COUNT(*) AS total_employees
FROM employees;

  • Counts all employees in the employees table.

  • Output:

total_employees
120

Example: SUM

SELECT SUM(salary) AS total_salary
FROM employees;

  • Calculates the total salary of all employees.

Example: AVG, MIN, MAX

SELECT AVG(salary) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM employees;

  • Returns average, minimum, and maximum salary in a single query.

2. Grouping Data with GROUP BY

GROUP BY lets you summarize data by categories, instead of the whole table.

Example: Count Employees by Department

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

  • Groups employees by department and counts how many are in each.

department        num_employees
Sales        25
HR        15
IT        30

Example: Total Salary by Department

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

  • Shows total salaries for each department.

3. Filtering Groups with HAVING

WHERE filters individual rows, but if you want to filter groups, use HAVING.

Example: Departments with More Than 20 Employees

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 20;

  • Only shows departments with more than 20 employees.

Example: High Salary Departments

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

  • Shows departments with average salary above 60,000.

4. Tips for Using GROUP BY

  • Always include all non-aggregated columns in the GROUP BY clause.

  • Combine GROUP BY with ORDER BY to sort results.

  • Use HAVING after aggregation, unlike WHERE.

Aggregate functions and GROUP BY are essential for summarizing large datasets. With these, you can:

  • Count records.

  • Calculate totals and averages.

  • Group data by categories.

  • Filter groups using HAVING.

These tools form the foundation for data analysis in SQL.

Comments

Post a Comment

Popular posts from this blog

Database Integration in FastAPI (SQLAlchemy CRUD)

  Introduction So far, we’ve built APIs without storing data.But real-world applications need a database to: Store user data. Manage products. Persist application state. In this blog, we’ll connect FastAPI with a database using SQLAlchemy and perform CRUD operations . What is SQLAlchemy? SQLAlchemy is a powerful Python ORM (Object Relational Mapper) that allows you to: Interact with databases using Python code. Avoid writing raw SQL queries. Work with models instead of tables. Step 1: Install Dependencies pip install sqlalchemy (For SQLite, no extra driver needed) Project Structure Update app/ ├── main.py ├── database/ │ ├── connection.py ├── models/ │ ├── item.py ├── schemas/ │ ├── item.py ├── routes/ │ ├── item.py Step 2: Database Connection app/database/connection.py from sqlalchemy import create_engine from sqlalchemy . orm import sessionmaker , declarative_base DATABASE_URL = "sqlite:///./test.db" engine = create_engine ( DAT...
Read more

Middleware & CORS in FastAPI

  Introduction As your API grows, you may need to: Log requests Modify responses Handle cross-origin requests This is where Middleware and CORS (Cross-Origin Resource Sharing) come into play.They help control how requests and responses flow through your application. What is Middleware? Middleware is a function that runs: Before a request reaches your route After a response is returned Think of it as a pipeline layer between client and server. Request Flow with Middleware Client → Middleware → Route → Middleware → Response Creating Custom Middleware from fastapi import FastAPI , Request import time app = FastAPI () @ app . middleware ( "http" ) async def log_requests ( request : Request , call_next ): start_time = time . time() response = await call_next ( request ) process_time = time . time() - start_time print ( f"Request: { request . url} | Time: { process_time } " ) return response What This Middle...
Read more

Python Data Handling

Python Data Handling for Beginners In Python, data handling means storing, changing, and using data in different ways. In this post, you will learn: Lists Tuples Dictionaries Sets String Methods Type Conversion 1. Python Lists :Lists store many values in one variable.                fruits = ["apple", "banana", "mango"]                                                                       Method                          Use                                            append()          ...
Read more