Excel Functions in SQL

Today we’ll be learning about SQL functions. We have functions in SQL similar to Excel.

The following Table ‘cricket’ we have.

 

AVG (Average) Function

AVG function will return the average of that column. As we can see in the below image that it returns the average of ‘Inns’ column. The query will be as following.

Select AVG( column_name) from Table_name;

 

 

COUNT Function

COUNT functions returns the number of values for the condition. Here it is returning the number of rows with the country ‘SA’.

Select COUNT(column) from table;
Select COUNT(DISTINCT column) from table; for Distinct values

 

 

SUM Function

It returns the sum of all the column values for the given condition. Here it is summing up the all the runs of Country ‘India’.

Select SUM(column) from table;

 

 

 

UPPER and LOWER Function

It converts all the characters in upper/lower case. As we can in the below image.

Select UPPER(column) from table;           To convert to upper case

Select LOWER(column) from table;           To convert to lower case

 

LENGTH Function

Length function return the length of the value according to the query.

Select LENGTH(column) from table;

 

 

MID Function

MID function is used to extract the substring from a string. Here in the example it is returning the string starting from position 1 to position 4.

Select column MID(column, start_position, end_position) from table;

 

 

NOW Function

NOW function returns the current date and time of the system.

Select NOW();

 

REPLACE Function

REPLACE function works same as it works in Excel.

REPLACE ( String,string_to_replace,string_to_replace_with)

as you see in the image below from the ‘status’ column we replaced ‘le’ with ‘el’.

 

 

TRIM Function

TRIM function removes the extra spaces in a string.

TRIM(String)- Remove extra spaces from both the sides

LTRIM(string)- Remove extra spaces from the left side

RTRIM(string)- Remove extra spaces from the right side.

As we can see in the image below, TRIM function removed the extra spaces where ‘column1=1 or 3’.

 

Arithmetic Operations

In SQL we can use arithmetic operations same as in Excel like (+,-,*,/). In the image below we can see that the division and subtraction operation in the query. It is very simple to use the arithmetic operation.

 

 

 

So this was about the Excel like functions in SQL. I’ll add more functions. Check out Analytics Tuts for more tutorials.