Menu
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?
788
You can use aggregate function sum() inside COALESCE(). The below syntax returns the sum of all if the record exists otherwise 0 is returned.
If there are no rows, sum() will return null . It will also return null if all rows have a null balance.
To return Sum as '0' if no values are found, use IFNULL or COALESCE commands. The following is the syntax for IFNULL. SELECT IFNULL(SUM(NULL), 0) AS aliasName; Let us now implement the above syntax in the following query.
If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; If you need to arrange the data into groups, then you can use the GROUP BY clause.
"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.
193
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
