SUM()
function return sum of all the values of given numeric column.
I am
going to use following sample data.
mysql> SELECT * FROM employee; +----+---------+------+-------------+---------------------+------------+ | id | name | age | salary | mailId | city | +----+---------+------+-------------+---------------------+------------+ | 1 | Hari | 28 | 12345.6700 | hari@hari.com | Bangalore | | 2 | Sandesh | 31 | 98345.0000 | sandesh@sandesh.com | Trivendram | | 3 | Phalgum | 33 | 119345.6700 | phalgun@hari.com | Hyderabad | | 4 | Manju | 36 | 87666.8700 | manju@sandesh.com | Bangalore | | 5 | Rakesh | 26 | 38000.0000 | rakesh@hari.com | Bangalore | | 6 | Sankalp | 38 | 87645.6700 | sankalp@sankalp.com | Chenai | | 7 | Vadiraj | 40 | 12345.6700 | vadi@hari.com | Bangalore | | 8 | Prasob | 37 | 12345.6700 | prasob@sandesh.com | Trivendram | | 9 | Kesav | NULL | 123457.8900 | NULL | NULL | +----+---------+------+-------------+---------------------+------------+ 9 rows in set (0.00 sec)
Get sum of all salaries of employees
mysql> SELECT SUM(salary) FROM employee; +-------------+ | SUM(salary) | +-------------+ | 591498.1100 | +-------------+ 1 row in set (0.00 sec)
Get sum of all salaries of employees
city wise.
mysql> SELECT city, SUM(salary) FROM employee GROUP BY city; +------------+-------------+ | city | SUM(salary) | +------------+-------------+ | NULL | 123457.8900 | | Bangalore | 150358.2100 | | Chenai | 87645.6700 | | Hyderabad | 119345.6700 | | Trivendram | 110690.6700 | +------------+-------------+ 5 rows in set (0.00 sec)
You can
escape NULL values by using following query.
mysql> SELECT city, SUM(salary) FROM employee WHERE city IS NOT NULL GROUP BY city; +------------+-------------+ | city | SUM(salary) | +------------+-------------+ | Bangalore | 150358.2100 | | Chenai | 87645.6700 | | Hyderabad | 119345.6700 | | Trivendram | 110690.6700 | +------------+-------------+ 4 rows in set (0.00 sec)
No comments:
Post a Comment