Try adding: WHERE BrandID IS NOT NULL This will eliminate any rows where the value is null. It will have impact on the calculation of AVG, of course.
If you have multiple BrandID columns, just add "AND BrandID2 IS NOT NULL", etc. Stephen > 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 > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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
