On Tue, 2002-12-24 at 12:11, [EMAIL PROTECTED] wrote: > I wish to get a count of records contained in 1 table as compared to the > contents of a different table. What I have is a list of realtors in 1 > table (agents.name) The listings are contained in a different table > (listings) and each can be identified by their agent by listings.agent. > > Here's the query I've tried using alias and COUNT but it doesn't seem to > work. In fact it's telling me that the table (listings) doesn't even exist > when I know it does. > > SELECT name, COUNT(*) as cnt from agents where agents.name = > listings.agent;
SELECT name,COUNT(listings.*) as cnt from agents LEFT OUTER JOIN listings ON agents.name=listings.agent GROUP BY name; IMO, joining by name is a real bad idea. > > What I need to accomplish is to generate a report where it will list out > each agent in agents followed by the number of listings posted by them in > listings even if it is zero. > > TIA, > > Ed > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
