Alex Rivera | Logout

SELECT SUM returns a row when there are no records

Asked 2010-03-13T02:07:48.837
15

I'm finding some problems with a query that returns the sum of a field from a table for all the records that meet certain conditions. I expected to receive a "No records found' when there were no records, but instead I'm receiving a null result.

SQL> SELECT * FROM DUAL WHERE 1=2;

no rows selected
SQL> SELECT SUM(dummy) FROM DUAL WHERE 1=2;

SUM(DUMMY)
----------


SQL>

Is there any way to not receive any record in that case?

sql oracle-database sum
Edit
Report

2 Answers

1

You can filter out the null results with having

SELECT SUM(dummy) FROM DUAL WHERE 1=2 HAVING SUM(dummy) IS NOT NULL
answered 2010-03-13T02:14:51.257
1

The sum of zero numbers is equal to 0 (that's math). So it would be only natural for select sum(something) to return 0 if there are no records to sum, similarly to select count(*) that should return 0 if the count is 0 (no records satisfying the predicate). That's in an ideal world of course.

answered 2012-01-01T07:41:29.267

Your Answer