CODEWITHSUNDEEP
mysqlsqldefault-value
serdanarik
serdanarik

Reputation: 45

SQL Command Default Value

In my Ms Sql command, I want to get sum of C.HEIGHT's values

 SELECT SUM(C.HEIGHT) 
 FROM CIHAZ AS C 
 INNER JOIN ILISKI AS I ON I.REL_CIHAZ=C.ID 
 WHERE I.REL_DOLAP=2

This value is returning NULL but I want to give Default Value to sum(C.HEIGHT) .

How can we provide this?

Thanks.

Upvotes: 0

Views: 151

Answers (3)

davek
davek

Reputation: 22915

You can replace any NULLs inline with coalesce, as shree.pat18 has said.

Although, according to SQL standards, NULL values will be ignored during aggregation unless there are no matching rows at all as stated here:

http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html

SUM() returns NULL if there were no matching rows.

That means that you do not need to do this:

select sum(coalesce(c.height, 0))....

(just in case you or anybody else was wondering).

So a COALESCE around the entire query will do it, as Damien_The_Unbeliever shows.

select coalesce(<query goes here>, 0)....

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

You can use COALESCE but, since you're aggregating over all results, you're probably getting NULL because there are no rows. In that case, you need to wrap the entire query as a subquery to get COALESCE to act the way that we want:

SELECT COALESCE( 
(SELECT SUM(C.HEIGHT) 
 FROM CIHAZ AS C 
 INNER JOIN ILISKI AS I ON I.REL_CIHAZ=C.ID 
 WHERE I.REL_DOLAP=2),
0 /* <-- default */) as Result

Upvotes: 2

shree.pat18
shree.pat18

Reputation: 21757

Use COALESCE to provide a default value in case the column value is NULL, like so:

SELECT COALESCE(SUM(C.HEIGHT), yourdefaultvalue)
FROM CIHAZ AS C 
INNER JOIN ILISKI AS I ON I.REL_CIHAZ=C.ID 
WHERE I.REL_DOLAP=2

COALESCE is an ANSI standard function, and you can use it in the way shown above in MySQL, Oracle and SQL Server.

Upvotes: 1

Related Questions