SQL Functions

SQL Aggregate Functions

• COUNT - Return the no of values in a specifed cloumn

 SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

• SUM-The SUM of values in a Specified 

SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;

• AVG-the Average of the Values in a specified column

• MIN-the Smallest value in a specified column

 SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;

• MAX-the largest Value in a specified column

SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;
 These function operate on a single column of a tal and return a single value.COUNT,MIN,MAX apply the both numeric and non numeric values while sum and AVG apply to only numeric values.An Aggregate Function can be used only in the SELECT and HAVING clauses.They cannot be used in WHERE clauses.if the SELECT includes an Aggregate Functions and no GROUP by is being used group data together,then no item in the SELECT list can include any reference to a column ,unless that that column is the argument to an Aggregate Functions.


for Example-SELECT staffNo,COUNT (Salary) From Staff;

This is ILLEGAL,as the query does not have a Group by clouse and the column StaffNo in the SELECT list is used outside an Aggregate Function.

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.
Useful scalar functions:

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed