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

upobDaPlaya
upobDaPlaya used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
Perfect.  Thanks for the explanation.  Makes total sense.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial