2 
5 
9
- SQL has many built-in functions for performing calculations on data.
SQL Aggregate functions
- SQL aggregate functions return a single value, calculated from values in a column.
Useful aggregate functions:
AVG() – Returns the average value
COUNT() – Returns the number of rows
FIRST() – Returns the first value
LAST() – Returns the last value
MAX() – Returns the largest value
MIN() – Returns the smallest value
SUM() – Returns the sum
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
The AVG() Function
- The AVG() function returns the average value of a numeric column.
- SQL AVG() Syntax
|
1 |
SELECT AVG(column_name) FROM table_name |
SQL COUNT() function
- The COUNT() function returns the number of rows that matches a specified criteria.
- SQL COUNT(column_name) Syntax
|
1 |
SELECT COUNT(column_name) FROM table_name |
SQL COUNT(*) Syntax
- It returns total no of data in a table.
|
1 |
SELECT COUNT(*) FROM table_name |
SQL COUNT(DISTINCT column_name) Syntax
- It returns count distinct column.
|
1 |
SELECT COUNT(DISTINCT column_name) FROM table_name |
The FIRST() function
- The FIRST() function returns the first value of the selected column.
- SQL FIRST() Syntax
|
1 |
SELECT FIRST(column_name) FROM table_name |
The LAST() function
- The LAST() function returns the last value of the selected column.
- SQL LAST() Syntax
|
1 |
SELECT LAST(column_name) FROM table_name |
The MAX() function
- The MAX() function returns the largest value of the selected column.
- SQL MAX() Syntax
|
1 |
SELECT MAX(column_name) FROM table_name |
The MIN() function
- The MIN() function returns the minimum value of a selected column.
- SQL MIN() Syntax
|
1 |
SELECT MIN(column_name) FROM table_name |
The SUM() function
- The SUM() function return the sum of values in the selected column.
- SQL SUM() Syntax
|
1 |
SELECT SUM(column_name) FROM table_name |
The GROUP BY Statement
- The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.
- SQL GROUP BY Syntax
|
1 2 3 4 |
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name |
The Having Clause
- The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions
- SQL HAVING Syntax
|
1 2 3 4 5 |
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value |
The UCASE() function
- The UCASE() function converts the value of a field to uppercase.
- SQL UCASE() Syntax
|
1 |
SELECT UCASE(column_name) FROM table_name |
The LCASE() function
- The LCASE() function converts the value of a field to lowercase.
- SQL LCASE() Syntax
|
1 |
SELECT LCASE(column_name) FROM table_name |
The MID() function
- The MID() function is used to extract characters from a text field.
- SQL MID() Syntax
|
1 |
SELECT MID(column_name,start[,length]) FROM table_name |
The LEN() function
- The LEN() function returns the length of the value in a text field.
- SQL LEN() Syntax
|
1 |
SELECT LEN(column_name) FROM table_name |
The ROUND() function
- The ROUND() function is used to round a numeric field to the number of decimals specified.
- SQL ROUND() Syntax
|
1 |
SELECT ROUND(column_name,decimals) FROM table_name |
The NOW() function
- The NOW() function returns the current system date and time.
- SQL NOW() Syntax
|
1 |
SELECT NOW() FROM table_name |
The FORMAT() function
- The FORMAT() function is used to format how a field is to be displayed.
- SQL FORMAT() Syntax
|
1 |
SELECT FORMAT(column_name,format) FROM table_name |
Different SQL functions
Related Posts:
Leave a Reply
