Sql - Sum of Positive and Negative Numbers Using Subquery

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