Introduction
Aggregate functions in SQL are used to perform calculations on a group of rows. They provide valuable insights into data by summarizing information across multiple records. In this blog post, we'll explore various aggregate functions and the GROUP BY clause in Oracle SQL, using the STUDENTS table as an example.
Our Sample Table: STUDENTS
Column Name | Data Type |
STUDENT_ID | NUMBER(10) |
STUDENT_NAME | VARCHAR2(50) |
ADDRESS | VARCHAR2(100) |
PHONE | VARCHAR2(20) |
CITY | VARCHAR2(50) |
DISTRICT | VARCHAR2(50) |
STATE | VARCHAR2(50) |
Aggregate Functions
COUNT(): Returns the number of rows.
SUM(): Calculates the sum of a numeric column.
AVG(): Calculates the average of a numeric column.
MIN(): Returns the minimum value of a column.
MAX(): Returns the maximum value of a column.
Example:
To find the total number of students:
SELECT COUNT(*) AS total_students
FROM STUDENTS;
Grouping Results
The GROUP BY clause is used to group rows based on one or more columns. It allows you to perform aggregate functions on each group.
Syntax:
SELECT column_name1, column_name2, ...
FROM table_name
GROUP BY column_name1, column_name2, ...;
Example:
To find the number of students in each city:
SELECT CITY, COUNT(*) AS total_students
FROM STUDENTS
GROUP BY CITY;
Grouping Rules
All Non-Aggregate Columns Must Be in the GROUP BY Clause: Any column that is not part of an aggregate function must appear in the GROUP BY clause.
Aggregate Functions Can Be Used with or Without GROUP BY: If you don't use GROUP BY, the aggregate function will be applied to the entire table.
HAVING Clause: The HAVING clause is used to filter the grouped results based on aggregate functions.
Example:
To find cities with more than 10 students:
SELECT CITY, COUNT(*) AS total_students
FROM STUDENTS
GROUP BY CITY
HAVING COUNT(*) > 10;
Combining Aggregate Functions and Grouping
You can combine multiple aggregate functions and grouping to get more detailed information.
Example:
To find the average grade for each student's major (assuming a MAJOR column):
SELECT MAJOR, AVG(GRADE) AS average_grade
FROM STUDENT_GRADES
GROUP BY MAJOR;
Conclusion
Aggregate functions and the GROUP BY clause are powerful tools for analyzing and summarizing data in SQL. By understanding how to use them effectively, you can gain valuable insights into your data and make informed decisions.
Comments