On Monday 19 August 2002 09:12 pm, Jocelyn Fournier wrote: except, leave off the 'f.' on 'f.count(*)'. Remember there is only one count of records, the count of returned rows from the join, there is no need to qualify that with a table identifier... (and I think it would be a syntax error if you do).
Also, about the GROUP BY. If you have the group by, then you will have only one record in your result set for each unique s.name, and your count(*) will return the number of times each s.name appears in table s. Table f won't really do much, except that any row in s that doesn't have a match of its id column with a row in table f will simply not appear at all. My suspicion is that you're wanting to know how many times there is an f with a given id for each unique value of s.name. That is a tough one.... Really you will have to group on some other value in f which is unique to each row in f. So you could then have something like. SELECT COUNT(*) AS fcount, s.name, f.unique, WHERE f.id = s.id GROUP BY s.name, f.unique and I believe that would accomplish what you want (though you will end up with a row in your result you don't really care about, but at least the count is there). > Hi, > > Try: > SELECT f.count(*), s.name FROM first f,second s WHERE f.id = s.id GROUP BY > s.name; > > Regards, > Jocelyn > > ----- Original Message ----- > From: "Mark S Lowe" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, August 20, 2002 3:02 AM > Subject: Count(*) using a join? > > > I need to do a join with a count...but for some reason I canıt figure out > how to do it using MySQL. Iım hoping that this pseudo code can help you > give > > me an answer: > > select > > > > f.count(*), > > s.name > > > > from > > > > first f, second s > > > > where > > > > f.id = s.id > > How do I get a sql result like: > > Count Name > --------------------------- > 12343 Test > > Iıve attempting adding the group by function as documented, but MySQL has > some demands I donıt understand. > > > Thank you in advance! > > Mark > > > > --------------------------------------------------------------------- > 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
