SQL SUM Command

SELECT sum( column_name ) FROM table_name
SQL Sum 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

Sum of values of columns in MySQL table with GROUP BY , IN and 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
classtotal_mark
Four250
Three140
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
SQL Math References SQL sum in multiple columns over() partition Add sum to different table
Answers of questions posted by visitors on SQL sum command MySQL query



Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    zin el ouni

    16-09-2010

    how Ican execute Querie union (2 table with many field and one Field numéric), with this field i want make an Operator sum
    JTM

    11-08-2011

    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
    harosa

    17-01-2016

    thank you for an excellent explanation!
    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.....
    smo1234

    11-02-2019

    Use round() function

    06-02-2021

    Thanks Bro U R So Good Thank U Soo Much

    17-02-2021

    I have selected and listed ProductId,ProductName,Price
    SELECT ProductId,ProductName,Price
    FROM Products
    WHERE Price >= 50.00
    Now how do I get a total of this while still retaining the listed items
    Thanks in Advance
    John R

    18-02-2021

    Use over() with Partition.

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer