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.

Table-compressed

 

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;

 

AVG-compressed

 

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

 

COUNT-compressed

 

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;

 

SUM-compressed

 

 

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

upper-compressed

 

LENGTH Function

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

Select LENGTH(column) from table;

 

length-compressed

 

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;

 

MidFunction-compressed

 

NOW Function

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

Select NOW();

 

now-compressed

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’.

REPLACE original table-compressed

 

 

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’.

TRIM ORIGINAL TABLE-compressed

 

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.

 

Manual calc-compressed

 

 

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *