How do I sum only negative field values in MS SQL reports

Posted on 2005-03-25
Medium Priority
Last Modified: 2012-06-27
Hi Xperts.

This might be an easy answer for you guys, but its a tough question for me!

I've built a simple SQL report which needs to total wirless accounts which have negative message balances on their unit's.

and then sum only the negative values in order to produce a total of the negative numbers ignoring the positive numbers.

EXAMPLE of what I need:

Record 1 Value  2
Record 2 Value -2
Record 3 Value -2

SUM of ALL records =  -4 which ignores the positive numbers

My current formula doesn't work because it 'sums' everything instead of just negative numbers.


Thank you very much for your help.

Question by:wtsumpes
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13634688
Without seeing your table structure it is a little difficult to know, but something like this should work:

Select Account, SUM(OverageRemain)
From  TableName
Where SIGN(OverageRemain)  != 1
Group By Account
LVL 14

Accepted Solution

amyhxu earned 2000 total points
ID: 13658876
=  Sum(iif(Fields!OverageRemain.Value >= 0, 0, Fields!OverageRemain.Value))
LVL 14

Expert Comment

ID: 13688675
Have you tried my solution? It should work for you.

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question