Sorry. my mistake. I needed to change your t.id with my t.fishid. Many thanks for all your help. All this on April Fool's day!
----- Original Message ----- From: "Dina Hess" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, April 01, 2002 8:37 AM Subject: Re: Help With Query > 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 > > > > > > > > > > > > > > > ______________________________________________________________________ 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/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
