> -----Original Message----- > From: rudy [mailto:r937 at interlog.com] > Sent: Tuesday, October 15, 2002 1:46 PM > To: thelist at lists.evolt.org > Subject: Re: [thelist] SQL: COUNTing and SUMming and whatnot > > > hi chris howdy! let me list the tables and their fields. productTracking: * id (autonumber) * ptProduct (number, comes from id.PossibleProducts) * ptReferralSource (number, comes from id.PossibleAdverts) PossibleProducts: * id (autonumber) * ppName (text, name of product) PossibleAdverts: * id (autonumber) * paName (text, name of product) > >1. a count total for each item in #2. > > (i.e. how many times was productB recorded?) > > select 'occurrences by product: ' > , a.productname > , count(o.occurrenceid) as numberofoccurrences > from ads a > left outer > join occurrences.o > on a.productid = o.productid using the info i gave above, i came up with this query. SELECT 'occurrences by product: ', PP.ppName, COUNT(productTracking.ptProduct) AS numberofoccurrences FROM PossibleProducts PP LEFT OUTER JOIN ptProduct.productTracking ON PP.id = ptProduct.productTracking" but i have a feeling that's all wrong. for one, it didn't work (gave me an error that said "syntax error in join operation") and two, i wasn't sure what you meant by "occurences.o". i hadn't tried the others yet. thanks so far! chris.