Search code examples
sqlselectarithmetic-expressions

Adding Columns in SQL

I have this equation in a select query

Monitoring Fees"+"CCN Fees"+"WkRpt Fees"+"MonRpt Fees"+"Client Dashboard Fees"+"Take
Control Servers Fees"+"Patch Management Servers Fees"+"Remote Background Servers Fees"
as "Total Capped Server Fees"

A problem I am seeing is that it will only add if there is value in each of the columns but I need it to add even if there is no data.

For remote background server, fees do not kick in until September of 2013. So Total capped server fees equation isn't adding add the others until there is values for everything so it is starting in september 2013 instead of for all years.

Solution

  • Some of the columns are returning NULL. You need to use ISNULL to return 0 instead of NULL. For example:

    ISNULL("Monitoring Fees", 0) + 
ISNULL("CCN Fees", 0) + 
ISNULL("WkRpt Fees", 0) + 
ISNULL("MonRpt Fees", 0) + 
ISNULL("Client Dashboard Fees", 0) + 
ISNULL("Take Control Servers Fees", 0) + 
ISNULL("Patch Management Servers Fees", 0) + 
ISNULL("Remote Background Servers Fees", 0) as "Total Capped Server Fees"

    I am assuming you are using SQL Server.

    If you are using MySQL then the equivalent function is IFNULL. For example:

    IFNULL("Monitoring Fees", 0) + 
IFNULL("CCN Fees", 0) + 
IFNULL("WkRpt Fees", 0) + 
IFNULL("MonRpt Fees", 0) + 
IFNULL("Client Dashboard Fees", 0) + 
IFNULL("Take Control Servers Fees", 0) + 
IFNULL("Patch Management Servers Fees", 0) + 
IFNULL("Remote Background Servers Fees", 0) as "Total Capped Server Fees"