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 > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
