Filling empty SQL query-cells with a value

Posted At : August 27, 2007 3:29 PM | Posted By : Pablo "Wizard of" Vos
Related Categories: Coldfusion,SQL

Whenever you run into a query where you have to do an average - AVG()-, or some other aggregation based on it's value it might come in handy to have a value there, even if the field is empty.

For example, the SQL-average of...

Item1 = 2.50 euro
Item2 = 5.00 euro
Item3 = empty (null)

Will be 3.75 euro ((2.5+5)/2) since empty fields won't be taken into account. In some cases I want an empty value to be considered in the average, in this case it may be 0.00 euro, so the outcome will be 2.50 euro ((2.5+5+0)/3).

The SQL statement IsNull comes in quite handy in this context. Read on...

I load CFQUERY like this:

<cfquery datasource="#application.dsn#" name="productQuery">
SELECT AVG(isnull(prod_price, 0)) as prod_avg
FROM tblProducts
</cfquery>

Basically you tell a field to go two ways: if there's a value, return that, otherwise return zero.

This also works for aggregation inside the isnull():

<cfquery datasource="#application.dsn#" name="productQuery">
SELECT isnull(SUM(prod_price), 0) as prod_price_SUM
FROM tblProducts
</cfquery>

This example forces that there should always be a value, even if the sum of my selection is empty. This prevents division by zero errors, when dividing inside your code.

It works for textfields just as well:

<cfquery datasource="#application.dsn#" name="productQuery">
SELECT isnull(prod_description, 'No description available') as prod_description
FROM tblProducts
</cfquery>