This works for me: SELECT count(t.id) as FishCount, f.CommonName, s.FishSubCategory FROM FishDB AS f, FishInv AS t, FishSubCategory AS s WHERE f.id=t.id And f.id = s.id AND t.TankID=2 GROUP BY f.CommonName, s.FishSubCategory
----- Original Message ----- From: Frank Mamone To: CF-Talk Sent: Sunday, March 31, 2002 8:25 PM Subject: Re: Help With Query Can't pull out count for each individual FishID. I tried grouping using the Group attribute in CFQUERY and the grouping of course works, but all I'm missing is the SUM for each group. Is there a function to sum items in a group? ----- Original Message ----- From: "Dina Hess" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Sunday, March 31, 2002 3:38 PM Subject: Re: Help With Query > Whoops! You'll need to change that query slightly to pull your count out... > > First line should be: > > Select count(t.id) as FishCount,... > > And if you have a *lot* of fish in an MS Access db, you'll probably want to change the inner join syntax to increase performance (I did it the lazy way <grin>). > ----- Original Message ----- > From: Dina Hess > To: CF-Talk > Sent: Sunday, March 31, 2002 1:53 PM > Subject: Re: Help With Query > > > Frank, > > Try this: > > SELECT count(t.id), f.CommonName, s.FishSubCategory > FROM FishDB AS f, FishInv AS t, FishSubCategory AS s > WHERE f.id=t.id And f.id = s.id AND t.TankID=2 > GROUP BY f.CommonName, s.FishSubCategory; > > Dina > > > > > ----- Original Message ----- > From: Frank Mamone > To: CF-Talk > Sent: Sunday, March 31, 2002 12:39 PM > Subject: Re: Help With Query > > > OK. Here's an example: > > FishDB Id CategoryID SubCategoryID SciName CommonName Comment > 52 1 1 Anomalochromis thomasi African Butterfly Cichlid > > > Id CategoryID SubCategoryID SciName CommonName Comment > 25 1 1 Teleogramma brichardi Brichard's Slender Cichlid > > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > FishCategory Id Category Comments > 1 Freshwater > > > > > > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > FishSubCategory Id FishSubCategory Category Comment > 1 Cichlids 1 African & American > > > > > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > FishInv Id TankId FishId > 20 2 52 > 21 2 52 > 22 2 52 > 23 2 25 > > > > Given only the Tank ID I want: > > 3 African Butterfly Cichlid > 1 Brichardis Slender Cichlid > > > Thanks > > > ----- Original Message ----- > From: "Dina Hess" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Sunday, March 31, 2002 1:25 PM > Subject: Re: Help With Query > > > > Frank, > > > > Illuminate me. Just out of curiousity, what type of data might you store > in the main and sub fish categories? > > > > Dina > > ----- Original Message ----- > > From: Frank Mamone > > To: CF-Talk > > Sent: Sunday, March 31, 2002 12:17 PM > > Subject: Re: Help With Query > > > > > > Hi Dina, > > > > This is what I have: > > > > > > 1. FISHDB - Main fish DB. Each Fishs as a category, Subcategory and > unique > > ID. I have Lookups for the Category and SubCategory. I think this aprt > is > > fine. > > > > 2. TANKS - Stores Tanks by Owner. TankID is unique. I think this is OK. > > > > 3. FISHINV - Stroes 1 record for each fish added. > > > > Example if I add 3 Goldfish it will contain three records with > essentially > > the same information.This may be bad. > > > > Now , I'd like to query FISHINV to output for example: > > > > 3 GoldFish > > 5 Yellows Labs > > 3 Cobalt Blues > > > > I should be able to remove any qty of a specific fish. > > > > Thanks for your answer. I'll take a closer look and see if it helps me. > > > > Frank > > > > > > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
