SQL Server – Sum Positive Items and Return Negative Items

sql serversql-server-2008t-sql

I am needing to find a way to SUM() all of the positive values for num and return the SUM() of all positive numbers and an individual row for each negative number. Below is a sample DDL:

Create Table #Be
(
    id int
    , salesid int
    , num decimal(16,4)
)

Insert Into #BE Values
    (1, 1, 12.32), (2, 1, -13.00), (3, 1, 14.00)
    , (4, 2, 12.12), (5, 2, 14.00), (6, 2, 21.23)
    , (7, 3, -12.32), (8,3, -43.23), (9, 3, -2.32)

And this is my desired output (positive numbers for each salesid SUM() and negatives get an individual line returned):

salesid    num
1          26.32
1          -13.00
2          47.35
3          -12.32
3          -43.23
3          -2.32

Best Answer

Try this:

SELECT   salesid, sum(num) as num
FROM     #BE
WHERE    num > 0
GROUP BY salesid
UNION ALL
SELECT   salesid, num
FROM     #BE
WHERE    num < 0;

If you want both the sum values in one row then you must create a maxValue (and minValue) function and use this as sum(maxValue(0, num)) and sum(minValue(0, num)). This is described in: Is there a Max function in SQL Server that takes two values like Math.Max in .NET?

Related Solutions

SQL Server – How to Return Rows One at a Time from Variable Table

This query should do what you want and avoid any issues with 1 to many relationships messing up the SUMs

SELECT i.job_no, installment_billing_amount - transaction_billing_amount
FROM (
        SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount 
        FROM [FCU_LIVE].[dbo].[jm_installment] 
        GROUP BY job_no
    ) i
    INNER JOIN (
        SELECT jm_work_order.job_no, SUM(jm_wo_transaction.billing_amount) transaction_billing_amount
        FROM [FCU_LIVE].[dbo].[jm_work_order]
            INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
                AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
                AND jm_wo_transaction.override = 'N'
        GROUP BY jm_work_order.job_no
    ) t ON i.job_no = t.job_no
WHERE   i.job_no IN ( SELECT    job_no
                      FROM      @job_nos )

It is easier to follow as a CTE query.

WITH 
    installmentSum AS 
    (  
    SELECT job_no, SUM(jm_installment.billing_amount) installment_billing_amount 
    FROM [FCU_LIVE].[dbo].[jm_installment] 
    GROUP BY job_no
    )
    ,
    transactionSum AS 
    (
    SELECT jm_work_order.job_no, SUM(jm_wo_transaction.billing_amount) transaction_billing_amount
    FROM [FCU_LIVE].[dbo].[jm_work_order]
        INNER JOIN [FCU_LIVE].[dbo].[JM_WO_TRANSACTION] ON jm_work_order.wo_no = jm_wo_transaction.wo_no
            AND jm_work_order.wo_seq = jm_wo_transaction.wo_seq
    WHERE jm_wo_transaction.override = 'N'
    GROUP BY jm_work_order.job_no
    )
SELECT j.job_no, installment_billing_amount - transaction_billing_amount
FROM @job_nos j
    INNER JOIN installmentSum i ON j.job_no = i.job_no
    INNER JOIN transactionSum t ON i.job_no = t.job_no;

SQL Server – Return Values from Left Table Only Once

You can replace LEFT by OUTER APPLY.

Query:

SELECT [rs].[ID]
    , [rs].[event1]
    ,  [Exists] = CASE WHEN [bph].[uuid] IS NOT NULL THEN 'Yes' ELSE 'No' END
FROM #robschneider rs
OUTER APPLY (
    SELECT TOP(1) [uuid]
    FROM #bigponyhorses 
    WHERE [empid] = @empid 
        AND [rs].[uuid] = [uuid]
) [bph]

Or:

SELECT [rs].[ID]
    , [rs].[event1]
    , [Exists] = COALESCE([bph].[Exists], 'No')
FROM #robschneider rs
OUTER APPLY (
    SELECT TOP(1) [Exists] = 'Yes' 
    FROM #bigponyhorses 
    WHERE [empid] = @empid 
        AND [rs].[uuid] = [uuid]
) [bph]

Or:

SELECT [rs].[ID]
    , [rs].[event1]
    , [Exists] = COALESCE(  
        (
            SELECT TOP(1) [Exists] = 'Yes' 
            FROM #bigponyhorses 
            WHERE [empid] = @empid 
                AND [rs].[uuid] = [uuid]
        )
        , 'No')
FROM #robschneider rs

Output:

ID  event1  Exists
1   cycle   Yes
2   run     Yes
3   walk    No