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.
