Answer includes, what is scalar functions in SQL and aggregate functions with examples and difference between scalar and aggregate functions.
SQL Scalar functions:
SQL scalar functions return a single value, based on the input value. For example, if we have to calculate the length of the string or want to convert letters in upper case or lower case then just we have to pass the string as an input and scalar function will return the required value.
Let’s say table is CITY containing 2 fields city id and city name. So, LEN() scalar function will calculate the length of name of the city.
Select cityname , len(city name) as LengthofCityName from CITY
The above statement selects the “cityname “ and length of the values in the “cityname ” column from the “CITY” table
List of scalar functions in SQL :
- 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
SQL Aggregate functions:
Aggregate functions are used to operates on data sets of a column of a table in SQL Database and return a result.
For example, we may want to get the sum of data of all rows of a column from a table then we can use SUM () aggregate function. Similarly, if we want to find the minimum elements of all rows of a column then we need use MIN () aggregate function.
Since, these functions are used to operates on data sets of a column, they are known as aggregate functions.
List of Aggregate functions in SQL:
- AVG() – Returns the average value of all rows
- COUNT() – Returns the number of rows
- FIRST() – Returns the first value of all rows.
- LAST() – Returns the last value
- MAX() – Returns the largest value
- MIN() – Returns the smallest value
- SUM() – Returns the sum
NOTES:
Aggregate functions can be placed only in SELECT statement or with the HAVING clause.