Aggregate functions in RDBMS

Objectives :

To understand the concept of aggregate functions in SQL and their role in summarizing and analyzing data in relational databases.

To learn the usage of common aggregate functions such as COUNT, SUM, AVG, MIN, and MAX with practical SQL examples.

To explore the application of aggregate functions with GROUP BY for generating meaningful reports like department-wise or category-wise summaries.

Introduction to Aggregate Functions in RDBMS

In Relational Database Management Systems (RDBMS), an aggregate function is a built-in SQL function that performs a calculation on a set of values and returns a single summarized value.

Aggregate functions are mainly used with the SELECT statement and are often combined with the GROUP BY clause to analyze data in groups.

They are widely used in real-world applications such as employee salary analysis, sales reports, student results, and inventory management.

🔹 Common Aggregate Functions

1. COUNT()
   Counts the number of rows (or non-NULL values in a column).

• Example:

        SELECT COUNT(*) FROM Employees;

•  ➝ Returns total number of employees.
  

2. SUM()
   

• Adds up the values of a numeric column.
  

    Example:
     SELECT SUM(Salary) FROM Employees;

•  ➝ Returns total salary paid to employees.
  

3. AVG()
   

• Finds the average of numeric values.
  

        Example:
         SELECT AVG(Marks) FROM Students;

•  ➝ Returns average marks of students.
  

4. MIN()
   

• Returns the smallest value in a column.

• Example:

SELECT MIN(Salary) FROM Employees;

•  ➝ Returns the lowest salary.
  
  

5. MAX()
   

• Returns the largest value in a column.
  

Example:
SELECT MAX(Salary) FROM Employees;

•  ➝ Returns the highest salary.
  
  

🔹 Aggregate Functions with GROUP BY

Aggregate functions become more powerful when combined with the GROUP BY clause.

Example:

SELECT Department, AVG(Salary)

FROM Employees

GROUP BY Department;

➝ Returns the average salary for each department.

Video Link : youtu.be/A4VojhOyoUI 

🔹 Key Points to Remember

• Aggregate functions always return a single value per group.
  
  

• They can be used in the SELECT and HAVING clauses.
  
  

• NULL values are ignored by functions like SUM(), AVG(), MIN(), MAX() but not by COUNT(*).
  
  

• Useful for data analysis, reporting, and decision making.

Here are some reference books for the topic Aggregate Functions in RDBMS (suitable for UG Computer Science):

📚 Reference Books

1. Database System Concepts
   
   

• Authors: Abraham Silberschatz, Henry F. Korth, S. Sudarshan
  
  

• Publisher: McGraw-Hill
  
  

• Widely used standard book covering SQL, RDBMS, and aggregate functions in detail.
  
  

2. Fundamentals of Database Systems
   
   

• Authors: Ramez Elmasri, Shamkant B. Navathe
  
  

• Publisher: Pearson
  
  

• Provides strong theoretical background with practical SQL examples.
  
  

3. Database Management Systems
   
   

• Authors: Raghu Ramakrishnan, Johannes Gehrke
  
  

• Publisher: McGraw-Hill
  
  

• Focuses on RDBMS concepts, SQL queries, and applications of aggregate functions.
  
  

4. Learning SQL
   
   

• Author: Alan Beaulieu
  
  

• Publisher: O’Reilly Media
  
  

• Beginner-friendly, good for practicing aggregate functions with simple examples.
  
  

5. SQL: The Complete Reference
   
   

• Authors: James R. Groff, Paul N. Weinberg
  
  

• Publisher: McGraw-Hill
  
  

• Comprehensive reference covering advanced SQL topics including aggregation and grouping.

• Reference Websites

• Tutorials & References

• 1. W3Schools – SQL Aggregate Functions
     Covers the main aggregate functions with examples.
     W3Schools

  2. Mode Analytics – SQL Aggregate Functions
     Intermediate-level explanation, good for working with real datasets.
     mode.com

  3. Simplilearn – SQL Aggregate Functions Tutorial
     Detailed guide with syntax, examples, use with GROUP BY / HAVING.
     Simplilearn.com

  4. GeeksforGeeks – Aggregate Functions in SQL
     Clear definitions, examples, and caveats.
     GeeksforGeeks

✨ Conclusion:
Aggregate functions in RDBMS are essential tools for summarizing large amounts of data. They help in generating insights such as totals, averages, maximum and minimum values, which are crucial for businesses and data analysis.

Quiz: https://docs.google.com/forms/d/e/1FAIpQLSf5v8AVY2_T2WSokK7X-AGb8LL-KPjGAIeGdze-RT1rNqkROw/viewform?usp=header