• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 97
  • Last Modified:

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

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
upobDaPlaya
Asked:
upobDaPlaya
1 Solution
 
Richard ObenchainCommented:
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
 
upobDaPlayaAuthor Commented:
Perfect.  Thanks for the explanation.  Makes total sense.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now