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
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
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 |
Hope these examples will help you in writing SQL Query better.