SQL Mathematical Functions, Concat and Trim Commands

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

Item	Quantity	Price	Reduction	Total
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_ID	Employee_Name	Age
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