Solved

Why is Null the result for a Sum calculation on a record not found

Posted on 2014-11-16
3
88 Views
Last Modified: 2014-11-16
For the below code why would the result return 1 row and a value of Null for SUM ?  In my case StockID 25 does not exist so  my assumption is that no rows would  be found ?  Why is 1 row returned ?


Select
 SUM(Interest) As Total
From
Table Stocks
WHERE
StockID = 25
0
Comment
Question by:upobDaPlaya
3 Comments
 
LVL 3

Accepted Solution

by:
Richard Obenchain earned 500 total points
ID: 40446212
No rows are found, but you're doing an aggregate without a group by statement, which means you'll always get one row result (regardless of whether there was anything to add up or not).

If you don't want any row to return, you could do this:

Select stockid, sum(interest) as Total where stockid = 25 group by stockid;

Because of the grouping, it won't return any rows unless there are valid records for Stockid = 25
0
 

Author Closing Comment

by:upobDaPlaya
ID: 40446231
Perfect.  Thanks for the explanation.  Makes total sense.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40446239
>For the below code why would the result return 1 row and a value of Null for SUM ?
As Richard states above now rows are found, and think of it this way:  NULL means there no rows found, and zero means rows were found and the average is zero.  These are two completely different answers, which could have erroneous downstream impacts if a zero is passed.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now