MySQL SUM Total of a numaric field of column of a table

SQL SUM Command

SELECT sum( column_name ) FROM table_name

Let us learn about the SUM sql command and how to use it in our tables. SUM command can be applied to numeric field and the total of the value is returned. Now let us apply this SUM command to this table and find out the total mark obtain by all the students. The SUM command will add all the values of the mark field and return to us. This is our table

id	name	class	mark
1	John Deo	Four	75
2	Max Ruin	Three	85
3	Arnold	Three	55
4	Krish Star	Four	60
5	John Mike	Four	60
6	Alex John	Four	55

Video Tutorial on Sum of Column values using GROUP BY , IN & CASE

We will apply the SUM command here like this to the field mark

SELECT sum( mark ) FROM `student`

sum(mark)
390

The command added all the values of the mark field and displayed. We can define some header like this also.

SELECT SUM(mark) as total_mark FROM `student`

total_mark
390

GROUP By with SUM Query

Now let us find out what is the total mark in each class. Here we can use the Group By command to find out the total mark obtained by each class

SELECT class, sum( mark ) as total_mark FROM`student` GROUP BY class

class	total_mark
Four	250
Three	140

You can see above that total mark of each class is displayed. Since we have two class in our table so the sql command has returned two class with total marks in each of them. We have to use Group By clause if we ask for the query to return any other field name other than the sum. Otherwise system will generate error.

Listing all columns along with aggregate functions like sum(), max(), min() using over() with partition

WHERE with SUM Query

We can add condition to the sql command to get our desired result. We can add one Where clause to the query to consider records for which mark is more than some value ( say 55 )

SELECT SUM( mark ) as total_mark, class FROM student WHERE  mark > 55 GROUP BY class

total_mark	class
195	Four
85	Three

Other sql commands like between can be used along with this sum command to find out required results.

IN with SUM Query

Sum of numbers for ID 1 to 4 ( using SQL IN )

SELECT sum( mark ) FROM `student` where id IN(1,2,3,4)

Math Formula with SUM Query

We will use one Product table where we will store buying price and selling price. We can get the total profit by using this query.

SELECT SUM(sell_price - buy_price) as Profit  FROM plus2_product

Output here is 5 as Profit.
We can use WHERE condition to find profit of a Product.

SELECT SUM(sell_price - buy_price) as Profit  FROM plus2_product WHERE product ='Product1'

Our Product table is here.

CREATE TABLE IF NOT EXISTS `plus2_product` (
  `product` varchar(20) NOT NULL,
  `buy_price` int(3) NOT NULL,
  `sell_price` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `plus2_product`
--

INSERT INTO `plus2_product` (`product`, `buy_price`, `sell_price`) VALUES
('Product1', 20, 22),
('Product2', 15, 20),
('Product3', 40, 43),
('Product1', 50, 45);

Find out non-numeric values present in a Column by using CONCAT

SQL file for SUM query

Video Tutorial on Below posted Questions with Answers

This article is written by plus2net.com team.

plus2net.com

▼ More on Math functions in SQL

SQL SUM Command

Video Tutorial on Sum of Column values using GROUP BY , IN & CASE

GROUP By with SUM Query

WHERE with SUM Query

IN with SUM Query

Math Formula with SUM Query

Video Tutorial on Below posted Questions with Answers