Transform and Summarize Data With Functions in SQL

A function can be defined as an operation that is performed with the objective to transform the appearance of data. Like other computer languages, SQL also supports the use of functions to manipulate data. Functions are usually applied to:

·         Convert data types of columns

·         Data calculation

·         Alter display formats

·         Provide collective output for groups of rows

Functions are divided into the following two categories:

    Single Row functions : As the name implies, the functions under this category act only on individual rows, and return a single result for every processed row. You can use these functions in SELECT, WHERE, and ORDER BY clauses. Single row functions are further categorized as follows:

·         Character functions: These functions accept character data as input and can return both character and number values.

·         Number functions: Number functions manipulate numeric data. They receive numeric input and return only numeric values.

·         Date functions: Besides presenting date and time data in some desirable format, these functions are also helpful in comparing date values and computing intervals between dates.

·         Conversion functions: With the help of these functions you can convert data type of some data to another data type. For example, to concatenate a number value to a character string, you convert the numeric value to character data type.

    Aggregate Functions : In contrast to the single row functions, the functions under this category operate on  groups of rows, and return one result per group. These functions are used to retrieve summarized data for analysis and reporting purposes. In this book, you’ll be practicing with the following most common aggregate functions, supported by all platforms.

·         Average function – AVG

·         Count function – COUNT

·         Maximum function – MAX

·         Minimum function – MIN

·         Sum function – SUM