CODEWITHSUNDEEP
sqloracle-databasesum
Jose L Martinez-Avial
Jose L Martinez-Avial

Reputation: 2265

SELECT SUM returns a row when there are no records

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?

Upvotes: 15

Views: 20204

Answers (8)

Hong Van Vit
Hong Van Vit

Reputation: 2986

SELECT SUM(dummy) FROM DUAL GROUP BY 1

Upvotes: 1

velop
velop

Reputation: 3224

You can group by the another metric. For example month and then sql also returns 0 rows:

SELECT SUM(dummy), DATE_FORMAT(day, '%Y-%m') as month
FROM DUAL WHERE 1=2
GROUP BY month

Upvotes: 0

Peter
Peter

Reputation: 6669

use the coalesce function of mysql and do:

SELECT COALESCE(SUM(where),0) FROM table WHERE condition = variable

Upvotes: 1

Vlad Patryshev
Vlad Patryshev

Reputation: 1422

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.

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

"I expected to receive a "No records found' when there were no records, but instead I'm receiving a null result."

Then do 

SELECT SUM(dummy) FROM DUAL WHERE 1=2 HAVING COUNT(*) > 0

That is, specify that you only want to return a summary where there were rows that were considered.

SELECT SUM(dummy) FROM DUAL WHERE 1=2 HAVING SUM(dummy) IS NOT NULL

is similar, but the COUNT(*) would return a summary row if there were only rows for which dummy was null, while the latter would not.

Upvotes: 23

Itay Moav -Malimovka
Itay Moav -Malimovka

Reputation: 53603

No - this is the behavior by design of the RDBMS in use here, Which, to me atleast, makes sense, as you are looking for a sum and not raw data

Upvotes: 1

Wolph
Wolph

Reputation: 80031

You can filter out the null results with having

SELECT SUM(dummy) FROM DUAL WHERE 1=2 HAVING SUM(dummy) IS NOT NULL

Upvotes: 1

Jeffrey L Whitledge
Jeffrey L Whitledge

Reputation: 59463

How about this:

select my_sum
from
(SELECT SUM(dummy) as my_sum FROM DUAL WHERE 1=2)
where
my_sum is not null

Upvotes: 6

Related Questions