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
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.
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.
Hi! can someone tell me how to sum two or more rows from one table but different categories?I have a table that summarises my sales per category but i want to generate a report that summarizes all the items sold per category and return it in one single row.Please help
ketan
15-01-2012
how to lisi a record from second to fifth from mysql db in php???
Rajesh
13-02-2012
Excellent Tutorial.
Habib Ullah
27-08-2013
Best Tutorial
julius pogi
24-09-2013
SELECT ACCOUNTTITLE,sum(debit),sum(credit) FROM `FIXEDJOURNALDTL` GROUP BY ACCOUNTTITLE
amar
28-09-2013
after getting the sum of column value in grid view .i want to display in text box.pls guard me...
Vivek
23-09-2015
Thank you for your tutorial.But I need id 1 to 4 sum values only, if any options is there? please tel...
smo
26-09-2015
Added this part by using SQL IN
shellu
08-10-2016
excellent
Aziz
10-02-2019
how to get Sum after Round a field in Group Clause
Select Sum (Round (Field ,2)) from Tabel Gruop By.....