SQL Aggregate functions are described in this article. SQL functions are like average , sum , count , first last etc.
SQL Aggregate functions
Consider the following Student table with ID, Marks, Age,Name as columns.
+----+-------+-----+-----------+ | Table Name - Student | +----+-------+-----+-----------+ | ID | MARKS | AGE | NAME | +----+-------+-----+-----------+ | 1 | 10 | 20 | MANIDEEP| | 2 | 40 | 10 | PAVAN | | 3 | 30 | 30 | YUSUF | | 4 | 20 | 15 | ROHIT | | 5 | 70 | 39 | AKHIL | +----+-------+-----+-----------+
-
AVG():
AVG() is SQL Aggregate function which calculates the average value of a given column.
Syntax: Select AVG(column_name) from table_name
Example: Below query will find the average “marks” of all the students.
Query:SELECT AVG(MARKS) FROM STUDENT; Result: 34.0
Example: Below query will find the average “marks” of all the students.
Query: SELECT AVG(AGE) FROM STUDENT; Result: 22.8
-
COUNT():
Count() is SQL Aggregate function which is used to count number of rows in select statement
Syntax: SELECT COUNT(column_name) FROM STUDENTS
Example: Below query will find the count of all the “Student” table row.
Query: SELECT COUNT(*) FROM STUDENT; Result: 5
Example: Below query will find the Count of the of all the “Student” table row whose “age” is more than 20.
Query:SELECT COUNT(*) FROM STUDENT WHERE AGE > 20; Result: 2
-
FIRST():
FIRST() is SQL Aggregate function which returns value of selected column of first row. It can be used in MS-ACCESS only.
Syntax: SELECT FIRST(column_name) FROM TABLE
Example: Below query will find the First Value of “Age” column from “Student” table.
Query:SELECT FIRST(AGE) FROM STUDENT; Result: 20
Example: Below query will find the First Value of “Marks” column from “Student” table.
Query:SELECT FIRST(MARKS) FROM STUDENT; Result:10
-
LAST():
LAST() is SQL Aggregate function which returns value of selected column of last row. It can be used in MS-ACCESS only.
Syntax: SELECT LAST(column_name) FROM TABLE;
Example: Below query will find the Last Value of “Age” column from “Student” table.
Query: SELECT LAST(AGE) FROM STUDENT Result: 39
Example: Below query will find the Last Value of “Marks” column from “Student” table.
Query:SELECT LAST(MARKS) FROM STUDENT Result:70
-
MAX():
MAX() is SQL Aggregate function which returns the maximum value of selected column in the table.
Syntax: SELECT MAX(column_name) FROM TABLE;
Example: Below query will find the First Maximum value of “Age” column from “Student” table.
Query: SELECT MAX(AGE) FROM STUDENT Result: 39
Example: Below query will find the Maximum Value of “Marks” column from “Student” table.
Query: SELECT MAX(MARKS) FROM STUDENT Result: 70
-
MIN():
MIN() is SQL Aggregate function which returns the minimum value of selected column in the table.
Syntax:SELECT MIN(column_name) FROM TABLE;
Example: Below query will find the First Minimum value of “Age” column from “Student” table.
Query: SELECT MIN(AGE) FROM STUDENT Result: 10
Example: Below query will find the Minimum Value of “Marks” column from “Student” table.
Query:SELECT MIN(MARKS) FROM STUDENT Result:10
-
SUM():
SUM() function calculates sum of all values of the selected column in the table
Syntax: SELECT SUM(column_name) FROM TABLE;
Example: Below query will find the sum of all values of “Marks” column from “Student” table.
Query:SELECT SUM(MARKS) FROM STUDENT Result:170
Example: Below query will find the sum of all values of “Age” column from “Student” table.
Query: SELECT SUM(AGE) FROM STUDENT Result:114
This is all about the SQL Aggregate functions. If you have any doubt mention in below comments or mail us your query.