Post reply

Sum +ve and -ve values from a column separately

  • March 7, 2008 at 2:05 am

    #71709

    Hi,

    I have table with a column which has both negative and positive values.

    I want to calculate the sum of positve values and the sum of negative values from that column separately. Is it possible.

    Plase help me.

    Thanks in advance

  • March 7, 2008 at 2:16 am

    #786508

    Use this:

    [Code]SELECT COUNT(MyColumn) AS PosTotal FROM MyTable WHERE MyColumn >0 UNION ALL

    SELECT COUNT(MyColumn) AS NegsTotal FROM MyTable WHERE MyColumn <0[/Code]

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • March 7, 2008 at 2:20 am

    #786510

    Hi there,

    you can create 2 scalar functions which calculate separately the sum of positive values and the sum of negative values:

    First function dbo.Function1 will return this:

    SELECT @sumPositive = sum(value) FROM Table where value > 0

    Second function dbo.Function2 will return this

    SELECT @sumNegative = sum(value) FROM Table where value < 0

    In the end after creating the functions you can see their values:

    SELECT dbo.Function1 as PositiveSum

    SELECT dbo.Function2 as NegativeSum

    Hope it helps. 😉

    Regards,

    Oana Schuster.

    PS:it's not really necessary to create functions .. but if you need these values several times, you don't have to rewrite the whole thing. 😛

  • March 7, 2008 at 2:22 am

    #786512

    Hi there,

    I think that using the COUNT function is not very good .. because it will not return the sum of the values, it will only count the number of rows which have in that column a positive value, and the number of rows which have in that column a negative value.

    Just replace the COUNT with SUM.

    Regards,

    Oana.

  • March 7, 2008 at 3:03 am

    #786521

    Thanks for your response. It works fine.

  • March 7, 2008 at 4:28 am

    #786540

    Hi ,

    Try this !!!

    select sum(case when app_amount = 0 then app_amount else 0 end ) '-ve Sum'

    from AutoMail_Salary

    Thanks,

    Amit Gupta

    MVP

  • March 7, 2008 at 11:14 pm

    #787010

    I am sorry, I should have indicated SUM instead of COUNT.

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • March 10, 2008 at 8:59 am

    #787418

    SELECT

    SUM(CASE WHEN ve < 0 THEN ve ELSE 0 END) AS Negative,

    SUM(CASE WHEN ve > 0 THEN ve ELSE 0 END) AS Positive

    FROM Table1

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply