Menu
How do you return 0 instead of null when running the following command:
SELECT MAX(X) AS MaxX FROM tbl WHERE XID = 1 (Assuming there is no row where XID=1)
625
MAX returns NULL when there is no row to select. For character columns, MAX finds the highest value in the collating sequence. MAX is a deterministic function when used without the OVER and ORDER BY clauses.
You can use COALESCE() along with aggregate function MAX() for this. Insert some records in the table using insert command. Display you all records from the table using select statement. Here is the case when table is empty.
UPDATE [table] SET [column]=0 WHERE [column] IS NULL; Null Values can be replaced in SQL by using UPDATE, SET, and WHERE to search a column in a table for nulls and replace them. In the example above it replaces them with 0.
As all of your values are null, count(cola) has to return zero.
or:
SELECT coalesce(MAX(X), 0) AS MaxX FROM tbl WHERE XID = 1 In SQL 2005 / 2008:
SELECT ISNULL(MAX(X), 0) AS MaxX FROM tbl WHERE XID = 1 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
