3. AVG (column)
The AVG function returns the average value of a selected column. It does not include NULL values in the calculation.
Syntax:
SELECT AVG (column) FROM Table
Example:
SELECT AVG (salary) FROM Employee
This example returns the average age for persons older than 30.
Result: 16240.90.
4. COUNT (Column)
The COUNT function returns the number of not NULL items in a group.
Syntax:
SELECT COUNT (column) FROM Table
Example:
SELECT COUNT (Age) FROM Employee Where salary > 15000
This example finds the number of persons with a value in the “Age” field in the “Persons” table.
Result: 7.
5. SUM (column)
The SUM function returns total sum of a selected column. It does not include NULL values in the calculation.
Syntax:
SELECT SUM (column) FROM Table
Example:
SELECT SUM (Salary) FROM Employee
This example returns the sum of all ages in the “person” table.
Result: 178650.
6. FIRST (column)
The FIRST function returns the value of the first record in the selected field.
Syntax:
SELECT FIRST (column) AS [expression] FROM table
Example:
SELECT FIRST (Age) AS lowest age
FROM Employee
ORDER BY Age
Result: 19.
7. LAST (column)
The LAST function returns the value of the last record in the selected field.
Syntax:
SELECT FIRST (column) AS [expression] FROM table
Example:
SELECT LAST (Age) AS highest age
FROM Employee
ORDER BY Age
Result: 45.
These functions are vital, as they reduce the number of lines in your code and enable you to perform complex SQL queries with ease. These functions operate on an entire group, table, or row, or a set, and significantly increase the scope of the language in terms of data manipulation, and by serving disparate user needs.
Join a discussion about this article in our forum.
]]>