SQL SUM() Function

The SQL SUM() is a function, and return total sum of a table column from query result.

The SQL SUM() function is supports only numeric column or an numeric field based expression.

It can be used in SQL SELECT statement as well in SQL WHERE clause.

We will examine how to query the total of all values in a column of a database table in this post. Let's first establish a table with some columns and some data before calculating the total of all values in a column.

Step 1: Creating a table :

Use the below syntax to create a table inside the database,

Syntax:

create table table_name( column_name 1 data type ( size ) ,
column_name 2 data type ( size) ,
. . . . column_name n data type ( size ) )

For purposes of illustration, we'll construct a department table and work with it. There will be 3 fields in the department table: deptid, deptname, and totalemployees. Use the following phrase to achieve this:

CREATE TABLE department( deptid integer ,
deptname varchar(20) ,
totalemployees integer );

The table will then be created. We must use the INSERT command to add values to the table. So let's see if we can add additional information to the department table we made:

Note: Values must be inserted in accordance with the prepared table. As an illustration, we made a department table with the columns deptid, varchar, and totalemployees. Therefore, a character, an integer, and an integer must each be inserted.

Step2: Now let us insert some rows into the department table using the below query:

INSERT INTO department values(1,'IT',32);
INSERT INTO department values(2,'CSE',56);
INSERT INTO department values(1,'ECE',28);

Following the same procedure, we've added a few rows to the table. Let's use the SELECT command to output the data from the table now:

SELECT * FROM department;

Note that * here stands for everyone. The complete table will be shown if we run this query.

Step 3: Add up all the values in a column.

We must utilize the sum() method for this. The column name must be provided as a parameter.

The SELECT query can be used to retrieve data from the table along with this sum() function.

Example: Here to find the sum of all values in a column.

SELECT SUM(totalemployees) FROM department;

Conclusion: Using the sum() function we can get the sum of values in a column using the column name.

In more complicated reports, a CASE WHEN statement is sometimes used with an SUM() function, which might be difficult for beginners. The SUM() method can be used for counting even though you're generally used to using it for summing values.

Example: This example will help you understand the concept better. I’ll use the table subject which has the following columns:

• id: the ID of the subject.
• name: the name of the subject.
• number_of_lectures: the number of lectures throughout the year.
• department: the department where the subject is taught.

The task is to count the number of both mandatory and elective subjects by department. In this example, every subject that has more than 20 lectures during the year is considered mandatory. Do you know how to solve this task? Here, let me help you:

SELECT  department,
        SUM (CASE
                WHEN number_of_lectures > 20 THEN 1
                ELSE 0
        END) AS mandatory_subjects,
        SUM (CASE
                WHEN number_of_lectures <= 20 THEN 1
                ELSE 0
        END) AS elective_subjects
FROM subject;

Finally, the result of the query is grouped by the column department. Here’s what this query will return as a result:

Explanation:

• Examine the code now! It begins by choosing the department column from the table's subject. Then there is the odd combination of an SUM() and a CASE WHEN. This statement states that the row is given the value 1 whenever the number_of_lectures is greater than 20. The allocated value is zero if the condition is not satisfied.
• The SUM() function will sum all those rows that have the assigned value equal to 1. Think for a moment; if you add all the 1s, what will you get? Exactly, it’s the same as if you’d counted the rows whose number_of_lectures is above 20. Using a CASE WHEN expression to assign values 0 or 1 to the table rows is just a little trick to make SUM() return the number of rows just like the COUNT() function would. The number of the subject with more than 20 lectures will be shown in the column mandatory_subjects.
• The same logic applies to the next CASE WHEN expression. The only difference is that the condition refers to 20 or fewer lectures, with the result shown in the column elective_subjects.

Example:

SELECT  department,
        SUM (CASE
                WHEN number_of_lectures > 20 THEN 1
                ELSE 0
        END) AS mandatory_subjects,
        SUM (CASE
                WHEN number_of_lectures <= 20 THEN 1
                ELSE 0
        END) AS elective_subjects
FROM subject
GROUP BY department;

The aggregate function SUM() in SQL Server determines the total of different values in an expression. utilise the DISTINCT keyword in the argument when using the SUM function.

Syntax:

SUM([ALL | DISTINCT ] expression)

1. ALL - informs SUM() to yield the total of all values, including duplicates. ALL is automatically utilised.
2. DISTINCT - Enables SUM() to compute the sum of only distinct values.
3. expression - A legal expression is any one that yields a precise or approximative numeric value. Keep in mind that the expression does not support aggregate functions or subqueries.

Example 1: Let’s create a new table for demonstration the difference between ALL and DISTINCT:

CREATE TABLE t(
    val INT
);

INSERT INTO t(val)
VALUES(1),(2),(3),(3),(4),(NULL),(5);

SELECT
    val
FROM
    t;

Example 2: The following statement returns the sum of all values in the val column:

SELECT
    SUM(val) total
FROM
    t;

Output:

Warning: Null value is eliminated by an aggregate or other SET operation.

Example 3: However, when we use the DISTINCT modifier, the SUM() function returns the sum of only unique values in the val column:

SELECT
    SUM(DISTINCT val) total
FROM
    t;

Output:

Warning: Null value is eliminated by an aggregate or other SET operation.

Example 4:

SELECT category,
  SUM(DISTINCT quantity)
FROM product
GROUP BY category;

Output:

If you look at the database, you’ll see that there are five items of clothing in stock:

When asked how many items of apparel are now in stock, the query returns 3, nevertheless. Why? due to the fact that the quantity column for products in the clothing category has the number "2" twice. Repeated values in the column are not taken into account when using DISTINCT.

Keep in mind that in the function argument, the DISTINCT keyword occurs before the column name or the expression.

Example 1: For this, use GROUP BY clause along with aggregate function SUM(). Let us first create a table:

Step 1: Create table

create table DemoTable(
   Name varchar(100),
   Score int
);

Step 2: Insert some records in the table using insert command:

insert into DemoTable values('Adam',50);
insert into DemoTable values('Bob',80);
insert into DemoTable values('Adam',70);
insert into DemoTable values('Adam',10);
insert into DemoTable values('Carol',98);
insert into DemoTable values('Bob',10);

Step 3: Display all records from the table using select statement:

select *from DemoTable;

Output:

Step 4: Following is the query to get the sum of columns for duplicate records in MySQL,

select Name,sum(Score) from DemoTable group by Name;

Output:

Example 2: Combine duplicate rows on column1 sum column2:

I have the following data.

Id      ParentId    ProductId   Quantity
215236  19297       16300319    60
215221  19297       16314611    6
215234  19297       16314670    1    <- Duplicate productid
215235  19297       16314670    2    <- Duplicate productid
215195  19297       16314697    20
215205  19297       16321820    75
215216  19297       16329252    10
215233  19297       16331834    9
215224  19297       16519280    40

Selecting unique records is easy. Or grouping the data on ProductId is also possible. I need a result that only contains a unique Product Id with the same parentid and the quantities summed up.

Following query to solve this,

SELECT MIN(id) id,
       ParentId, 
       ProductId, 
       SUM(Quantity) Quantity
FROM [source_table_name]
GROUP BY ParentId, 
         ProductId

Output:

Id      ParentId    ProductId   Quantity
215236  19297       16300319    60
215221  19297       16314611    6
215234  19297       16314670    3
215195  19297       16314697    20
215205  19297       16321820    75
215216  19297       16329252    10
215233  19297       16331834    9
215224  19297       16519280    40

The quantities of the duplicates:

215234  19297       16314670    1    <- Duplicate productid
215235  19297       16314670    2    <- Duplicate productid

should result in,

215234  19297       16314670    3

Example 1: Joins are used in queries to retrieve data from tables with complex relationships between them. This is typically done for reporting reasons, where the data being retrieved is summarised. Even in some cases, it is necessary to change the data from a single table in order to obtain summary data. Let's say we have a database called workers that contains the following records, which can be obtained by running a straightforward select query on that table.

SELECT * FROM workers;

The current circumstance requires us to determine the total yearly wage of each team's employees. The team ID and the team's total salary should be included in the result. In order to do this, we must utilize the group by statement, group the data according to the team ID, and sum the individual salaries.

SELECT team_id,
SUM(salary)
FROM
workers
GROUP BY team_id ;

The following output, along with team ids and their corresponding total salaries, will be produced when the aforementioned query statement is executed.

Example 2: Rows with identical values are grouped into summary rows using the GROUP BY clause. The ability to generate summary reports is quite helpful in the aggregate function. If we attempt to use the following SELECT statement to mix aggregate and non-aggregated data, we will obtain an error:

SELECT occupation, SUM(salary) AS "Total Salary"  
FROM employee_info;  

Example 3: The GROUP BY clause, however, does not throw an error when used. The SUM() function will be used in the following query to determine the total employee wage depending on each occupation:

SELECT occupation, SUM(salary) AS "Total Salary"  
FROM employee_info  
GROUP BY occupation;  

This search locates the employee linked to each occupation and totalizes their salary.

Example 3:Typically, the SUM function is used along with the GROUP BY clause. The values are obtained for a group of rows using GROUP BY. Before continuing with this instance, I advise reading Using GROUP BY in SQL or How Does SQL GROUP BY Work? if you are unfamiliar with the concept of GROUP BY.

You can see how many products fall under each category in the following search:

SELECT category,
  SUM(quantity) AS total_quantity
FROM product
GROUP BY category;

Output:

Because we want to view the category for which the sum is calculated in this instance, the category column is in the SELECT. The SUM() function, which adds the quantity values, comes next. When the GROUP BY clause is present, goods that have the same value in the category column are grouped together, and the sum is determined independently for each group. Last but not least, always place the GROUP BY clause after the FROM clause.

Take note that the estimated sum for the IT category is NULL. This is so because the quantity column in every row with a "IT" value in the category field is NULL. Since the quantity field for one item in the "toys" category is NULL, the remaining values in this category are added together.

Of course, you can also combine records and add sums from different columns.

You can use the SUM function in the HAVING clause to filter the sums of groups depending on a particular condition.

Example 1: The following example returns the customers who paid more than $200:

SELECT customer_id,
	SUM (amount) AS total
FROM
	payment
GROUP BY
	customer_id
HAVING SUM(amount) > 200
ORDER BY total DESC

Example 2: The filter criterion for the HAVING clause might include SUM(). For instance, in the following query, we only want rows with more than a particular number to show up in the result:

SELECT category,
  SUM(quantity) AS total_quantity
FROM product
GROUP BY category
HAVING SUM(quantity)>2;

Output:

The results were limited to the category of apparel. Why? because in order to be included in the outcome, the total number of goods must be larger than 2. This requirement is satisfied by the clothing column (with a total_quantity of 5). Due to their total quantities being under 2, the other categories are not visible.

In MySQL, the Sum() aggregate method is used. Sum queries are compatible with if conditions. To comprehend the if conditional sum query.

Example 1:

Step 1: let us create a table.

mysql> create table SumWithIfCondition
   -> (
   -> ModeOfPayment varchar(100)
   -> ,
   -> Amount int
   -> );

Step 2: Insert some records in the table using insert command. The query is as follows:

mysql> insert into SumWithIfCondition values('Offline',10);
Query OK, 1 row affected (0.21 sec)

mysql> insert into SumWithIfCondition values('Online',100);
Query OK, 1 row affected (0.16 sec)

mysql> insert into SumWithIfCondition values('Offline',20);
Query OK, 1 row affected (0.13 sec)

mysql> insert into SumWithIfCondition values('Online',200);
Query OK, 1 row affected (0.16 sec)

mysql> insert into SumWithIfCondition values('Offline',30);
Query OK, 1 row affected (0.11 sec)

mysql> insert into SumWithIfCondition values('Online',300);
Query OK, 1 row affected (0.17 sec)

Step 3: Display all records from the table using select statement. The query is as follows:

mysql> select *from SumWithIfCondition;

Output:

+---------------+--------+
| ModeOfPayment | Amount |
+---------------+--------+
| Offline       |     10 |
| Online        |    100 |
| Offline       |     20 |
| Online        |    200 |
| Offline       |     30 |
| Online        |    300 |
+---------------+--------+

Here is the sum query with if condition.

Case 1 - if for Online Mode of Payment

select sum(if(ModeOfPayment = 'Online',Amount,0)) as TotalAmount from SumWithIfCondition;

Output:

Case 2 - if for Offline Mode of Payment

select sum(if(ModeOfPayment = 'Offline',Amount,0)) as TotalAmount from SumWithIfCondition;

Output:

Example 1: You can use the SUM function to answer more challenging business questions such as get top 5 customers by sales as the following query:

SELECT customers.customerid,
    companyname,
    (SUM(unitprice * quantity) - SUM(unitprice * quantity) * discount) AS total
FROM
    orderdetails
INNER JOIN
    orders ON orders.orderid = orderdetails.orderid
INNER JOIN
    customers ON customers.customerid = orders.customerid
GROUP BY customers.customerid
ORDER BY total DESC
LIMIT 5

Example 2: SQL SUM GROUP BY TOP 5 customers

To choose just 5 rows from the result set, we utilise the LIMIT clause. The LIMIT clause is supported by both MySQL and PostgreSQL. To accomplish the same result with Microsoft SQL Server, use the SELECT TOP operator as in the following query:

SELECT TOP 5
    customers.customerid,
    companyname,
    (SUM(unitprice * quantity) - SUM(unitprice * quantity) * discount) AS total
FROM
    orderdetails
INNER JOIN
    orders ON orders.orderid = orderdetails.orderid
INNER JOIN
    customers ON customers.customerid = orders.customerid
GROUP BY customers.customerid
ORDER BY total DESC;

Example 3: The following query uses the SUM() function to get the 5 best selling products:

SELECT p.productid,
    p.productname,
    (SUM(o.unitprice * quantity) - SUM(o.unitprice * quantity) * discount) total
FROM
    orderdetails o
INNER JOIN
    products p ON p.productid = o.productid
GROUP BY p.productid
ORDER BY total DESC
LIMIT 5;

Example 4: The queries with SUM/GROUP BY and LIMIT:

You can also restrict how many rows of grouped data are returned. The SUM aggregate function and GROUP BY clause are utilised in this instance. In order to have all rows, two queries are run: one without the LIMIT clause and one with it.

Query 1: Have a look at dummy data output:

SELECT Product_name, SUM(quantity) As 'Total Quantity'
 FROM boot_table
 GROUP BY Product_name;

Query 2:

SELECT Product_name, SUM(quantity) As 'Total Quantity'
 FROM boot_table
 GROUP BY Product_name
 Limit 2;

Example 1: We have shown how to utilise the sum function to determine a column's overall value in a MySQL table. We will now discover how to obtain a query for the sum in many columns and for each table entry. We will slightly alter our student table to improve comprehension by inserting marks from several subjects for each record. The table will now look like this.

id	name	        class social science   math
1	John Deo	Four	75	84	78
2	Max Ruin	Three	54	67	85
3	Arnold	        Three	78	65	90
4	Krish Star	Four	50	51	53
5	John Mike	Four	80	78	89

To display the total of each student's grades, we will create a query. Keep in mind that we will not be using the SQL sum function in this case. This is the code.

SELECT id, name, class,( social + science + math) AS total
FROM student_sum

Output:

id	name	        class	total
1	John Deo	Four	237
2	Max Ruin	Three	206
3	Arnold	        Three	233
4	Krish Star	Four	154
5	John Mike	Four	247

Multiple columns SUM:

As you can see, the sum above represents the total of each student's grades. In our table, this is the horizontal sum. Now, we'll attempt to obtain the total number of students' grades across all topics. Thus, the total must be recorded in each of the three columns.

SELECT sum(social + math + science ) as total 
FROM student_sum;

Result:

Example 1: One of my coworkers requested me for help while writing a report on how to display the total of just positive column values in a new column and the sum of only negative column values in a new column. Even though SSRS Report makes it simple to do it, he needed sql code to fulfil his requirements.

So below is the Sample code with Solutions

Step 1: Creating a test table with Both Position and Negative Values

create table value
(Testvalues int)

Step 2:

insert into value values(1),(-2),(3),(-4),(5),(-6)

Select * from Values

If you will calculate the total of positive values it will be 9 and for negative values it is -12.

Step 3: So below is the Query:

select
  SUM(case when Testvalues>0 then Testvalues else 0 end)SumPositiveValue,
       SUM(case when Testvalues<0 then Testvalues else 0 end)SumNegativeValues
       from value

Output:

Example 2: For this, you can use CASE statement.

Step 1: Let us first create a table:

create table DemoTable(
   Id int,
   Value int
);

Step 2: Insert some records in the table using insert command ,

insert into DemoTable values(10,100);
insert into DemoTable values(10,-110);
insert into DemoTable values(10,200);
insert into DemoTable values(10,-678);

Step 3: Display all records from the table using select statement:

mysql> select *from DemoTable;

Output:

Step 4: Following is the query to display the sum of positive and negative values from a column in separate columns:

select Id,
  sum(case when Value<0 then Value else 0 end) as Negative_Value
   from DemoTable
   group by Id;

Output:

Example 1: Assume that when computing the sum of values in a column that also contains NULL values, MySQL's SUM() function will disregard the NULL values and instead compute the sum of the remaining data. To better comprehend it, use these details from the table "employee":

Select * from Employee

Output:

Assume that the SUM() function is used to calculate the total salary of the employees in the preceding table and that it ignores NULL values. The necessary result set will be generated by the following query:

Select SUM(Salary) from Employee;

Output:

It can also be verified with the help of the following query:

Select SUM(Salary) from Employee WHERE Salary IS NOT NULL; 

Output:

Example 1: The SQL Server aggregate functions SUM, COUNT, MAX, MIN, and AVG can now be used with an OVER Clause, in case you weren't aware.

Without utilizing a GROUP BY clause, an OVER clause allows you to obtain individual record values as well as aggregate values at various levels. Additionally, you can create rolling averages, running totals, etc.

Aggregate To Different Levels with OVER (PARTITION BY ….)

The sample below displays each individual order record together with the overall tally of all sales, the annual revenue for the order's year, and the customer's total sales.

Here is the code for the above query:

SELECT YEAR(OrderDate), SalesOrderID, CustomerID, TotalDue,
		SUM(TotalDue) OVER() AS 'Total Business Sales', 
		SUM(TotalDue) OVER (PARTITION BY YEAR(OrderDate)) AS 'Total Annual Sales',
		SUM(TotalDue) OVER (PARTITION BY CustomerID) AS 'Total Customer Sales'
FROM Sales.SalesOrderHeader
ORDER BY CustomerID, YEAR(OrderDate)

Explanation:

SUM(TotalDue) OVER() AS 'Total Business Sales'

This expression generates a global total for the entire data set. The data is not partitioned in any way. The "Total Business Sales" column always has the same value in each record as a result.

SUM(TotalDue) OVER (PARTITION BY YEAR(OrderDate)) AS 'Total Annual Sales'

This expression asks SQL Server to create an annual sales total and group (partition) the data by the YEAR of the orderdate. You'll observe that each frequent year has the same value for this number.

SUM(TotalDue) OVER (PARTITION BY CustomerID) AS 'Total Customer Sales'

This expression asks SQL Server to create a customer sales total and group (partition) the data by CustomerID. If the CustomerID for an order is the same, you will observe that this value is similar.

All aggregate functions support the usage of the OVER clause.

Example 2: In the example below, each SalesOrderDetail record is shown along with the order's total amount ordered, average order quantity, number of items, lowest order quantity, and highest order quantity.

SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total Quantity Ordered'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Average Quantity Ordered'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Number Of Items On Order'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Lowest Quantity Ordered'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Highest Quantity Ordered'
FROM Sales.SalesOrderDetail 

Method 1: Running Totals With The ORDER BY Sub Clause:

The ORDER BY sub clause enables a running total to be generated.

The following example shows the monthly revenue for each month, along with the running total for the year.

SELECT DISTINCT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth,
	SUM(TotalDue) 
	OVER(PARTITION BY YEAR(OrderDate),MONTH(OrderDate) 
    ORDER BY YEAR(OrderDate),MONTH(OrderDate)) AS "Monthly Revenue", 
	SUM(TotalDue) 
	OVER(PARTITION BY YEAR(OrderDate) 
	ORDER BY MONTH(OrderDate)) AS "Running Revenue Total"
FROM Sales.SalesOrderHeader
ORDER BY YEAR(OrderDate), MONTH(OrderDate)

The totals for each month are different, and the fourth column displays how the total has increased during the year. However, the running total resets at the beginning of each new year.

If we look at December 2011, the total for the entire year is 14155699.525.

Looking at June 2011, the sum for May and June 2011 is 1074117.4188.

SUM(TotalDue)
OVER(PARTITION BY YEAR(OrderDate)
 ORDER BY MONTH(OrderDate)
 ) AS "Running Revenue Total"

Both a PARTITION and ORDER BY sub clause are present in the OVER clause for the fourth column, "Running Revenue Total."

The group is identified as the YEAR by the PARTITION BY.

The ORDER BY specifies that we assess the annual sum at the end of every month.

Example 3: The annual total is assessed after each year without the ORDER BY Clause (the partition determines the order). This is demonstrated in the instance below:

Here is the code for the above example:

SELECT DISTINCT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth,
	SUM(TotalDue) 
	OVER(PARTITION BY YEAR(OrderDate),MONTH(OrderDate) 
	--ORDER BY YEAR(OrderDate),MONTH(OrderDate)
	) AS "Monthly Revenue", 
	SUM(TotalDue) 
	OVER(PARTITION BY YEAR(OrderDate) 
	--ORDER BY MONTH(OrderDate)
	) AS "Running Revenue Total"
FROM Sales.SalesOrderHeader
ORDER BY YEAR(OrderDate), MONTH(OrderDate)

Example 4: Say we want to know the value of all orders subdivided by location (City). In order to achieve this, we employ the GROUP BY and SUM() functions, as seen below.

SELECT City AS CustomerCity
,sum(amount) AS totalamount FROM [SalesLT].[Orders]
GROUP BY city
ORDER BY city

We are unable to use the non-aggregated columns in the SELECT statement on the result set. For instance, since [CustomerName] is not mentioned in the GROUP BY clause, we are unable to display it in the report.

If you attempt to use the non-aggregated column in the column list, SQL Server displays the following error notice.

SELECT statement

SELECT City AS CustomerCity, CustomerName,amount,
SUM(amount) OVER(PARTITION BY city) TotalOrderAmount
FROM [SalesLT].[Orders]

The PARTITION BY clause executes the aggregate, creates a shorter window (set of data rows), and displays it, as demonstrated below. This result also includes non-aggregated columns that you can see.

Example 5: In the example, we’ll go into more details about users. We’ll show users with their countries. Look at the table below:

country	registration_date	registered_users
England	2020-03-05	               25
England	2020-03-06	               12
England	2020-03-07	               10
Poland	2020-03-05	               32
Poland	2020-03-06	               15
Poland	2020-03-07	                6

The number of users for each country are displayed separately for each day. We will calculate a unique cumulative sum of registered users for each nation in this demonstration.

The query ...

SELECT country, registration_date,registred_users,
  SUM(registred_users)
  OVER (PARTITION BY country ORDER BY registration_date)
  AS total_users
FROM registration;

Calculates the sum of users for each day, first for users from England and then for users from Poland.

Output:

country	     registration_date	registered_users	total_users
England	       2020-03-05	      25	            25
England	       2020-03-06	      12	            37
England	       2020-03-07	      10	            47
Poland	       2020-03-05	      32	            32
Poland	       2020-03-06	      15	            47
Poland	       2020-03-07	       6	            53

Every registration day receives a running total for every nation. The column nation serves as the argument for the PARTITION BY clause in the OVER clause. By dividing rows into countries, this enables SQL to only compute running totals for those nations (instead of both countries together). Thus, during 2020-03-05 to 2020-03-07, there were 47 users in England. 53 users were registered overall in Poland within the same time period.

Example 1: In this post, we can discuss, how we can sum positive and negative numbers without subquery.

The sum of a positive and negative integer is 33, -33 respectively.

select SUM(Number) as [Sum] from SumPositiveNegative

Getting Sum value is 0.

select SUM(Number) as [Sum] from SumPositiveNegative where Number<=0 

select SUM(Number) as [Sum] from SumPositiveNegative where Number>=0

These two queries are each returning a result of -33 or 33, but we need the same result from just one query, not two.

Here, we can acquire the required result sets by using the case when statement.

select 
sum(case when Number>=0 then Number end) [PSum]
,Sum(case when Number<=0 then Number end ) [NSum] 
from SumPositiveNegative

The above query will return the expected result.

Example 2: For this, you can use CASE statement. Let us first create a table:

Step 1: Create Table

mysql> create table DemoTable(
   Id int,
   Value int
);

Step 2: Insert table

Insert some records in the table using insert command:

mysql> insert into DemoTable values(10,100);
Query OK, 1 row affected (0.15 sec)

mysql> insert into DemoTable values(10,-110);
Query OK, 1 row affected (0.10 sec)

mysql> insert into DemoTable values(10,200);
Query OK, 1 row affected (0.10 sec)

mysql> insert into DemoTable values(10,-678);
Query OK, 1 row affected (0.17 sec)

Step 3: Display the table

Display all records from the table using select statement:

mysql> select *from DemoTable;

Output:

Step 4: Execution of program:

Following is the query to display the sum of positive and negative values from a column in separate columns :

mysql> select Id,
   sum(case when Value>0 then Value else 0 end) as Positive_Value,
   sum(case when Value<0 then Value else 0 end) as Negative_Value
   from DemoTable
   group by Id;

Output:

Output:

Example 1: suppose I have this table

Additionally, I want to return the first row in which the total of all preceding cash reaches a specific amount:

Therefore, I should go back to row 3 if I want to return the first row where the total of all the preceding cash is larger than 500.

let see,How we do this using mysql statement?

using WHERE SUM(cash) > 500 doesn't work

You can only use aggregates for comparison in the HAVING clause:

GROUP BY ...
  HAVING SUM(cash) > 500

The HAVING clause requires you to define a GROUP BY clause.

To get the first row where the sum of all the previous cash is greater than a certain value, use:

SELECT y.id, y.cash
  FROM (SELECT t.id,
   t.cash,
   (SELECT SUM(x.cash)
    FROM TABLE x
    WHERE x.id <= t.id) AS running_total
  FROM TABLE t
  ORDER BY t.id) y
 WHERE y.running_total > 500
ORDER BY y.id
LIMIT 1

The column alias for the aggregate function can be referred to in the WHERE clause because it is used in a subquery.

In general, a condition in a SQL query's WHERE clause can only relate to one record. There is no inherent order in an RDBMS table, and the context of a WHERE clause is evaluated before any order has been established by an ORDER BY clause.

Example 2: Only those rows that meet the specified requirement can be summarised with precision. The where clause in the SQL statement can be used to do this. To better grasp this, let's use an example.

Table: Customers

The following SQL statement finds the sum of the values of ORDER_VALUE column where the ORDER_VALUE is greater than 8000 :

SELECT SUM(ORDER_VALUE)
FROM Customers
WHERE ORDER_VALUE > 8000;

Result: