Can't you just use isnull(BrandID,0)? Then you'll have 0's replacing null values, and your average won't be messed up.
Steven D Dworman ------------------------------------------------------------------------- Web Consultant Systems Administrator ComSpec International - http://www.comspec-intnl.com phone: 248.647.8841 cell: 734.972.9676 ------------------------------------------------------------------------- EMPOWER-XL ***Software for Higher Education*** http://www.empower-xl.com -----Original Message----- From: Bryan Stevenson [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 5:49 PM To: CF-Talk Subject: Re: SQL Query Problem I'd add in some AND "WHATEVER BrandIF Field" IS NOT NULL to weed out the NULL records Bryan Stevenson VP & Director of E-Commerce Development Electric Edge Systems Group Inc. p. 250.920.8830 e. [EMAIL PROTECTED] --------------------------------------------------------- Allaire Alliance Partner www.allaire.com ----- Original Message ----- From: "Brian Ferrigno" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, November 15, 2001 2:39 PM 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 > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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
