How to sum positive and negative values
Wrap your query in a subquery and perform some grouping and aggregation on it:
SELECT SUM(QTY) AS QTY, SUM(AMT) AS AMT, PART_NBR, A_NBR, [Date]
FROM
(SELECT.... YOUR EXISTING QUERY HERE)
GROUP BY PART_NBR, A_NBR, [Date]
This will group by the last 3 columns: PART_NBR, A_NBR, [Date]
and add the values in the first 2 columns per group, thus adding the positive values ot the negative values.
Here's an example taking the results from your query output, wrapped in a subquery:
CREATE TABLE #YourSubquery
([QTY] int, [AMT] DECIMAL(10,2), [PART_NBR] int, [A_NBR] varchar(4), [Date] int)
;
INSERT INTO #YourSubquery
([QTY], [AMT], [PART_NBR], [A_NBR], [Date])
VALUES
(-1, -11208.58, 101, 'A-10', 2013),
(8, 89668.64, 101, 'A-10', 2013),
(3, 46362.42, 102, 'A-10', 2013)
;
SELECT SUM(QTY) AS QTY, SUM(AMT) AS AMT, PART_NBR, A_NBR, [Date]
FROM
(SELECT * FROM #YourSubquery) t
GROUP BY PART_NBR, A_NBR, [Date]
DROP TABLE #YourSubquery
Output:
QTY AMT PART_NBR A_NBR Date
========================================
7 78460.06 101 A-10 2013
3 46362.42 102 A-10 2013
Caveat: this is based on the assumption your AMT
column is a numeric data type, as your output is showing a $
sign.
Find the sum of negative numbers and the sum of the positive numbers
Just use case
:
select sum(case when val > 0 then val end) as pos_sum,
sum(case when val < 0 then val end) as neg_sum
from t;
Find the sum of negative numbers and the sum of the positive numbers
Just use case
:
select sum(case when val > 0 then val end) as pos_sum,
sum(case when val < 0 then val end) as neg_sum
from t;
SQL Sum() returning postive and negative values
I think you need to re-aggregate your results:
with l as (
<your query here>
)
select period, account, sum(amount)
from l
group by period, account;
You can do the same thing with a subquery instead of a CTE.
mysql Table Sum of Positive and Negative Numbers
Just use a standard pivot query with separate conditional aggregations for the positive and negative numbers.
SELECT
ID,
SUM(CASE WHEN Score >= 0 THEN Score ELSE 0 END) AS Pos,
SUM(CASE WHEN Score < 0 THEN -1*Score ELSE 0 END) AS Neg,
SUM(Score) AS Diff
FROM results
GROUP BY ID
ORDER BY ID
Demo
MySQL sum negative and sum of positive values without sub query
You can use a CASE
and a JOIN
to accomplish this:
SELECT
a.id,
SUM(CASE v.vote when 1 then 1 else 0 end) as UpVotes,
SUM(CASE v.vote when -1 then 1 else 0 end) as DownVotes
FROM
tbl_answers a
INNER JOIN
tbl_answer_votes v
ON
v.id = a.id
GROUP BY
a.id
This returns the ID
from tbl_answers
and the two columns with the total votes of either value from tbl_answer_votes
. You didn't specify which (if any) other columns you'd want from either table, so you may have to adjust the column list in the SELECT
and the GROUP BY
portions to add additional columns.
Related Topics
Sql to Generate a List of Numbers from 1 to 100
Eliminate Rows Based on Created Timestamp
Multiple Query Same Table But in Different Columns MySQL
Reactjs Connection With Database
Combining Multiple Rows in SQL Server into One
Using Nullif to Divide by Zero
How to Insert an Image in Sqlite Database(Table)
Convert the Students Marks into Respective Grades
Mysql: Error Code: 1054. Unknown Column in 'Where Clause'
Oracle Query to SQL Server Query
Return Dummy Value If No Row Present for in Clause
How to Connect MySQL Workbench to Running MySQL Inside Docker
Extract String Part Between Single Quotes from Formula'S Divisor
How to Concatenate Many Rows With Same Id in SQL
Sql: Update Column With Increment Numbers Based on 2 Columns
How to Use SQL Like Condition With Multiple Values in Postgresql