Thanks, this works great in the MySQL server...I guess I've never used temp tables before, but when I try to run this in a PHP script, I get "table does not exist". How do I do this?
--- Harald Fuchs <[EMAIL PROTECTED]> wrote: > In article > <[EMAIL PROTECTED]>, > Daren Cotter <[EMAIL PROTECTED]> writes: > > > I have a table that keeps track of when members of > my > > site are mailed. The important fields in the table > > are: member_id, mail_id > > > I need to write a query that will return the # of > > members and # of mailings, like the table below: > > > # of mailings sent # of members > > ----------------------------------- > > 1 10,000 > > 2 20,000 > > ... ... > > > Meaning, there are 10,000 members that have been > sent > > 1 mailing, and 20,000 members that have been sent > 2 > > mailings. > > > Is this possible in one query? > > I'd do it like that: > > SELECT nmails, COUNT(member_id) > FROM ( > SELECT member_id, COUNT(mail_id) AS nmails > FROM tbl1 > GROUP BY member_id > ) AS nm > GROUP BY nmails; > > Since MySQL versions < 4.1.0 don't know about > derived tables, you'd need a > workaround: > > CREATE TEMPORARY TABLE nm > SELECT member_id, COUNT(mail_id) AS nmails FROM > tbl1 GROUP BY member_id; > > SELECT nmails, COUNT(member_id) AS nmem > FROM nm > GROUP BY nmails; > > > [Filter fodder: SQL query] > > --------------------------------------------------------------------- > 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 > __________________________________________________ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com --------------------------------------------------------------------- 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
