find total of positive and negative numbers separately inline, using only select and case statement

sort array with negative numbers followed by positive numbers
decode in oracle with multiple condition example
nested decode in oracle
oracle decode multiple columns
sum(decode) in oracle sql
alternate positive and negative numbers in array java
how to use decode in case statement in oracle
decode() requires exactly two arguments hive

I have a question. If I am given a table with positive and negative numbers and I want to find total of all positives and total of all negatives separately and display it in one line. I was able to come up with one solution (below). I wanted to check if it is possible to have a better solution using Select and CASE statement

id  amount
1   100
2   -10
3   50
4   -80
5   20
6   -20

positive negative
170         -110

My Solution:

    create table #temp (id int, amount int)
insert into #temp values (1, 100)
insert into #temp values (2, -10)
insert into #temp values (3, 50)
insert into #temp values (4, -80)
insert into #temp values (5, 20)
insert into #temp values (6, -20)

with positive as 
(select sum(amount) as posNum from temp where amount > 0)
, negative as 
(select sum(amount) as negNum from temp where amount < 0)
select *, (select * from negative) from positive 

Use conditional aggregation:

    SUM(CASE WHEN amount > 0 THEN amount END) AS positive,
    SUM(CASE WHEN amount < 0 THEN amount END) AS negative
FROM #temp;


positive    negetive
170         -110

How to separate positive and negative numbers into their own , Although we recommend that you use the CASE expression rather than the values from all orders are summed, only Sunday orders are added to the total, since the outer join would find a matching row in the eur_regions inline view. In this example, two transition types are defined: positive (POS) and negative (NEG)​. In this article, we are going to learn how to calculate the sum of positive and negative numbers separately on the basis of criteria. We will use SUMIF function to sum up the positive and negative numbers. Let’s take an example and understand:-We have an agency’s car sales data. In data we have sales details along with discount detail.

You can also use sign() to determine it is positive or negative number

select  sum(case when sign(amount) = 1 then amount end),
        sum(case when sign(amount) = -1 then amount end)
from    #temp

9.3 DECODE and CASE Examples :: Chapter 9. DECODE and , Below are the steps to convert [Ln Lp Rn Rp] to [Ln Rn Lp Rp] without using extra space. 1. Reverse Lp and Rn. We get [Lp] -> [Lp'] and [Rn] -> [Rn'] [  I want to separate negative numbers in a column from positive numbers Original Title: "I have a column with positive and negative numbers. I want to separate them into two columns one negative numbers and one positive numbers.

This will work:

1) For SQL Server

    SUM(IIF(amount > 0, amount, 0)) positive,
    SUM(IIF(amount < 0, amount, 0)) negative

2) For SQL Server and MySQL

    SUM(CASE WHEN amount > 0 THEN amount END) positive,
    SUM(CASE WHEN amount < 0 THEN amount END) negative

Rearrange positive and negative numbers with constant extra space , When you hire a member of the hard-core, you can usually count on having to budgeting—he only knows that he's always wanted a hi-fi set and can get one by In line with that policy, we've deliberately taken on a number of tough cases learned is that the attitude of the supervisor—positive or negative—can make or  There a list of multiple values including positive and negative values in a worksheet, and you need to separate them into two columns, one contains positive values only, the other has negative values only. Now this tutorial is talking about the methods on separate the positive and negative numbers into two columns in Excel.

Congressional Record: Proceedings and Debates of the Congress, This lesson of the Python Tutorial for Data Analysis covers grouping data with pandas .groupby(), Early departures show negative numbers. can derive a new column from existing data using an in-line function, or a lambda function. SELECT unique_carrier, COUNT(CASE WHEN arr_delay <= 0 OR arr_delay IS NULL  resultset is two columns one should show sum of positive values and other sum of negative values – manjunath uppar nov 23 '16 at 16:15 add a comment | 5 Answers 5

Pandas .groupby(), Lambda Functions, & Pivot Tables, Scores were contrasted with those of children in local comparison groups using most students demonstrated losses when compared to the standardization were asked individually to identify 52 cards, each with a single upper or lower case positive with several CBI academic competence scales, significantly negative  Sum of positive and negative numbers. Hi . if someone could help that would be great . Write a program to read in 10 integer values and output the sum of all the positive integers and the sum of all the negative integers.

Resources in Education, The amounts involved were especially large for 1988 and 1989 when a total of $27 Transactions associated with the recording of bonds acquired under and commissions in the reported values of securities transactions, rather than in the These estimates are shown separately in the case of assets (line 6b of Table 20)​  Re: Average formula with positive and negative numbers just want to show you the use function averageif for (separate positive and negative numbers -average) =AVERAGEIF(A1:A10,"<0") this is for negative numbers.

  • mysql and sql-server are two completely different products. Which one are you using?
  • Need to wait 10 min, before I can do it.
  • I was asked this in an interview and the interviewer was hell bent on this kind of answer, and I cannot think of something so simple. Thanks for your help man
  • Getting greedy, can you answer this as well…
  • @kushalbhola, I've answered it - you can check there