Contact Us
  
 
 
    SearchSearch   RegisterRegister   Log inLog in 

convert null value to 0 in integer data type MySQL database

convert null value to 0 in integer data type MySQL database

 
Post new topic   Reply to topic     Forum Index -> DataBase, RDBMS, MySql, Oracle, db2, SQL Server problems
View previous topic :: View next topic  
Author Message
abington



Joined: 08 Dec 2008
Posts: 2

PostPosted: Wed Dec 17, 2008 4:39 pm    Post subject: convert null value to 0 in integer data type MySQL database Reply with quote

Hi
Mysql database is returning null instead of zero value in integer data type.
And creating problem in sum function of Mysql.
Actually I need to add two fields in Mysql table.

Code:
SELECT sum(a), sum(b), sum(a)+sum(b) FROM test t group by id


sum(b) is here null
and output is coming as null of sum(a)+sum(b)
But I need output in integer which is not coming.

Therefore if I can convert null into 0 and do sum it will come correct answer

Should have to use if condition??
Back to top
View user's profile Send private message
wangmu



Joined: 08 Dec 2008
Posts: 4

PostPosted: Wed Dec 24, 2008 7:28 am    Post subject: Reply with quote

Obviously you will get null
when you add any number with null
5+null=null
5+0=5

converting null into 0 in mysql with function COALESCE

Code:
select COALESCE(sum(a),0),COALESCE(sum(b),0),(COALESCE(sum(a),0)+COALESCE(sum(b),0)) FROM test t group by id
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic     Forum Index -> DataBase, RDBMS, MySql, Oracle, db2, SQL Server problems All times are GMT
Page 1 of 1

 
Jump to:  
  Quick Post a Reply Topic
     
  Name (required)  
     
  Email Address (will not be published) (required)  
     
  Subject of topic (required)  
     
   
     
   
     
  Advertisement  
     
 
     


  More Links  
 
JSP Introduction
JSP Setup
Simple JSP Example
JSP Variable
Array Declaration in JSP
JSP Forms and User Input
Form Validation
JSP Cookies
JSP Session Object
Loop, content collection, iterator, conditional check in JSP
JSP Application Object
JSP Include
JSP Response Object
JSP Request Object
JSP Error Handling
JSP File Handling Object
JSP Action Tag
JSP Implicit Objects
JSP Declaration
JSP Directive
JDBC Introduction
JDBC Driver
JDBC Connection
JDBC Prepared/Statement
JDBC ResultSet
JDBC Close Connection object
JDBC Insert
JDBC Display Records
JDBC Update
JDBC Delete
JDBC Sorting
JDBC Query
JDBC Exception
JDBC Add Batch
JDBC Transaction/RollBack