Author Archives: Admin

SUM function in Mysql

This function used to find the sum of fields in various records

See the example below

Select * from mydoubts_data

————————————-
|id | jobid | name | salary | age|
————————————-
|1 | 121 | vasanthan| 2000 |32 |
|2 | 122 | John | 3000 |28 |
|3 | 123 | Kiran | 3400 |24 |
|4 | 154 | Raju | 2800 |38 |
|5 | 155 | Raju | 2906 |40 |
|6 | 143 | Kiran | 3400 |42 |

select sum(salary) from mydoubts_data

————————–
| SUM(salary) |
————————–
| 17506|
—————————

Select name,sum(salary) from mydoubts_data group by name

——————–
name | salary |
——————–
vasanthan | 2000
| John | 3000
| Kiran | 6800
| Raju | 5706

AVG function in mysql

using AVG function will return the average value of particular fields.

Example:

Select * from mydoubts_data

————————————-
|id | jobid | name | salary | age|
————————————-
|1 | 121 | vasanthan| 2000 |32 |
|2 | 122 | John | 3000 |28 |
|3 | 123 | Kiran | 3400 |24 |
|4 | 154 | Raju | 2800 |38 |
|5 | 155 | Raju | 2906 |40 |
|6 | 143 | Kiran | 3400 |42 |

Please see the result of below query

Select AVG(age) from mydoubts_data

—————-
| AVG(age) |
—————
| 34 |
—————

you will get the average of various records using group by clause

select name,AVG(age) from mydoubts_data group by name

————————————-
|id | jobid | name | salary | age|
————————————-
|2 | 122 | John | 3000 |28 |
|3 | 123 | Kiran | 3400 |33 |
|4 | 154 | Raju | 2800 |39 |
|1 | 121 | vasanthan| 2000 |31 |

MAX function in mysql

This function using to find out the maximum value in a recordset

Select * from mydoubts_data

————————————-
|id | jobid | name | salary | age|
————————————-
|1 | 121 | vasanthan| 2000 |31 |
|2 | 122 | John | 3000 |29 |
|3 | 123 | Kiran | 3400 |22 |
|4 | 154 | Raju | 2800 |35 |
|5 | 155 | Raju | 2906 |40 |
|6 | 143 | Kiran | 3400 |42 |

Depend on the above data you can find the records having maximum age

Select MAX(age) from mydoubts_data

—————-
| MAX(age) |
—————
| 42 |
—————

You can also use the query to find out the maximum of each record as follows

select id,jobid,name,max(age) from mydoubts_data group by name

——————————–
|id | jobid | name | age|
——————————–
|2 | 122 | John | |29 |
|6 | 143 | Kiran | |42 |
|5 | 155 | Raju | |40 |
|1 | 121 | vasanthan| |31 |

we can find out the maximum and minimum age from the record set

select MIN(age) leastval,MAX(age) maxval from mydoubts_data
———————
| leastval | maxval |
———————-
| 22 | 42 |
———————

Count function in mysql

counting the number of records in a table.

Example:

Select * from mydoubts_table;

————————————
|firstname | lastname | address |
————————————
|vasanthan | pv | india |
|nazar | gt | texas |
|raju | raina | sreelanka|
|gt | nazar | texas |
|Martin | jacob | Dubai |
|mydoubts.in | | newyork |
|wordsgallery| | Frankfurt|
————————————-

Select COUNT(*) from mydoubts_table;

————
| COUNT(*) |
————
| 7 |
————

Select COUNT(*) from mydoubts_table where firstname=’vasanthan’;

————
| COUNT(*) |
————
| 1 |
————

Union clause in mysql

union means joining more than tables.It will not create problems if tables having different columns

Example:

Suppose we have three tables as below
Select * from mydoubts_personal;

————————————
|firstname | lastname | address |
————————————
|vasanthan | pv | india |
|nazar | gt | texas |
————————————-

Select * from mydoubts_buisness

————————————-
|last_name | first_name | address |
————————————-
|raju | raina | sreelanka|
|gt | nazar | texas |
|Martin | jacob | Dubai |
————————————-

Select * from mydoubts_enterprise

————————-
|institution | location |
————————-
|mydoubts.in | newyork |
|wordsgallery| Frankfurt| |
————————

Union Query as below

Select firstname,lastname,address from mydoubts_personal

UNION

Select first_name,last_name,address from mydoubts_buisness

UNION

Select institution,”,location from mydoubts_enterprise;

————————————
|firstname | lastname | address |
————————————
|vasanthan | pv | india |
|nazar | gt | texas |
|raju | raina | sreelanka|
|Martin | jacob | Dubai |
|mydoubts.in | | newyork |
|wordsgallery| | Frankfurt|
————————————-

Select firstname,lastname,address from mydoubts_personal

UNION ALL

Select first_name,last_name,address from mydoubts_buisness

UNION

Select institution,”,location from mydoubts_enterprise;
————————————
|firstname | lastname | address |
————————————
|vasanthan | pv | india |
|nazar | gt | texas |
|raju | raina | sreelanka|
|gt | nazar | texas |
|Martin | jacob | Dubai |
|mydoubts.in | | newyork |
|wordsgallery| | Frankfurt|
————————————-

BETWEEN in Mysql

This clause will replace greater/less than conditions

Example:

Select * from mydoubts_data

————————————-
|id | jobid | name | salary | age|
————————————-
|1 | 121 | vasanthan| 2000 |31 |
|2 | 122 | John | 3000 |29 |
|3 | 123 | Kiran | 3400 |22 |
|4 | 154 | Raju | 2800 |35 |

Select * from mydoubts_data where age>=29 and age<=31; ------------------------------------- |id | jobid | name | salary | age| ------------------------------------- |1 | 121 | vasanthan| 2000 |31 | |2 | 122 | John | 3000 |29 | above query can be replaced using between as below Select * from mydoubts_data where age between 29 and 31; ------------------------------------- |id | jobid | name | salary | age| ------------------------------------- |1 | 121 | vasanthan| 2000 |31 | |2 | 122 | John | 3000 |29 |