SQL Mathematical Functions, Concat and Trim Commands

    3 Votes

In this article, we will discuss about various Mathematical Operator like Average, Count, Sum, Min, Max, etc. that can be used in SQL queries to simplify complex mathematical operations.

SQL Average

The "Average" keyword is used to find average of specified column in a table or the number of rows that match a specific criterion. To find the average prices from sales table given below
ItemQuantityPriceReductionTotal
Item1 5 400 10 2000
Item2 6 300 10 1800
Item3 3 15 5 45
Item4 2 30 5 60
Item5 2 300 5 600
Item6 6 400 10 2400

select avg(price) Average-Price from sales
The result of the above mentioned query is as shown below

Average-Price
240.833

SQL Count

SQL Mathematical Function, Count keyword is used to find the no of rows in a table or the number of rows that match a specific criterion. To find the no of rows in sales table following SQL query is used.

select count(*) Sales-Count from sales
Sales-Count
6

SQL MAX

SQL Max keyword is used to find maximum value of specified column in a table or the number of rows that match a specific criterion. To find the maximum prices from sales table following SQL query is used.

select max(price) Max-Price from sales
Max-Price
400

SQL Min

SQL Min keyword is used to find minimum value of specified column in a table or the number of rows that match a specific criterion. To find the maximum prices from sales table following SQL query is used

select min(price) Min-Price from sales
Min-Price
15

SQL Sum

SQL Sum keyword is used to find total value specified column in a table or the number of rows that match a specific criterion. To find the total prices from sales table following SQL query is used

select sum(price) Total from sales
Max-Price
1445

SQL Concat

SQL concat function is used to append columns or append string to a columns. For example, to append employee name & Age of the table given below, following query is used.

Employee_IDEmployee_NameAge
1 John 28
2 Alex 24
3 James 35
4 Roy 22
5 Kay 44

For oracle

select employee_name || '_' || age from employee

For SQL Server

select employee_name + '_' + age from employee

For MYSQL

select concat(employee_name , '_' , age) from employee

Output of the query is given below.

Employee_Name_Age
John_28
Alex_24
James_35
Roy_22
Kay_44

SQL Trim

Trim keyword is used to remove a space from a column. To apply trim keyword on employee table following SQL query is used

select trim(employee_name) from employee
Employee_Name
John
Alex
James
Roy
Kay

To trim a specific character from right side, following SQL query is used.

select rtrim(employee_name,'y') from employee where employee_name in ('Roy','Kay')
Employee_Name
Ro
Ka

To trim a specific character from left side, following SQL query is use.

select ltrim(employee_name,'R') from employee where employee_name in ('Roy')
Employee_Name
oy

Hope these examples will help you in writing SQL Query better.