You can use Coalesce() or ISNULL() to avoid the NULL values, but you'll still have to keep from including that column in the equation or your division will come out wrong. i.e. you need to add only those columns that have values, then divide by the number of columns that had values to get the average. You should look into using CASE for the conditional logic. Eric
-----Original Message----- From: Brian Ferrigno [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 2:40 PM To: CF-Talk Subject: SQL Query Problem I am having a problem trying to think of the correct SQL statement I need. It's late in the day and my brain is completely fried. What I am tring to do is get the average price of one column based a an ID that is passed to the SQL query. For example if a BrandID of 1 is passed into the query it should output an average price of $4.00 The problem arises when one of the BrandID(BNID) columns doesn't contain any of the #ID# numbers in it. The value that it returns is NULL which causes the rest of the SQL statement to return NULL as a result even if the other columns contain at least one #ID# in the BNID column. For example passing a BrandID of 24 will return a resultset of NULL because it is not in the second or third BRANDID column. Hopefully I made some sense. Below is the partial table design and SQL statements I am using in my script. Any help would be great. TABLE DESIGN BNID1_3 Price1_3 BNID2_3 Price2_3 BNID3_3 Price3_3 1 $2.00 10 $0.00 1 $5.00 24 $0.00 1 $5.00 23 $0.00 SQL STATEMENT select (select avg(Price1_3) from temp1 where BNID1_3=#ID#)+ (select avg(Price2_3) from temp1 where BNID2_3=#ID#)+ (select avg(Price3_3) from temp1 where BNID3_3=#ID#) AS AVGPRICE ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
