No, you would not want to use isnull(BrandID,0). This would cause the number of IDs by which to divide the price total to be incorrect. For example, if you have 10 brands, but 2 of them have NULL fields, the total price should be divided by 8. However, by your proposed method, substituting a 0 (which is what the isnull function would do) would cause the total price to be divided by 10 because technically there is a value for that brand now, hence skewing your average. I would definitely use an IS NOT NULL clause to filter out the wrong fields.
Be very careful about this, as I have seen that mistake before, and it wreaks havoc! Dave. ----Original Message Follows---- From: "Steven Dworman" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: CF-Talk <[EMAIL PROTECTED]> Subject: RE: SQL Query Problem Date: Fri, 16 Nov 2001 08:13:37 -0500 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 > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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
