Re: Help with Query

Tue, 18 Mar 2003 00:44:56 -0800

OK, attempt number two:

Let's see what you are after is the number of emails that a member has received (say 25), and then you also want the number of members that have received x-number (say 25) emails. Is this even close to what you want?

I think the only way to answer the question(s) is to use more than one query (specially since MySQL doesn't support sub-selects).

Here's what I came up with: (it's probably not pretty and more than likely not completely correct, but I haven't been playing with MySQL than long).

CREATE TEMPORARY TABLE member_counts
SELECT member_id, COUNT(mailing_id) AS mail_count FROM member_mailings
GROUP BY member_id;

This puts the number of mailings into a temp table.

Now we do:

SELECT mail_count, COUNT(member_id) FROM member_counts
GROUP BY mail_count;

I *think* that these to step should give you what you are after.

jeff


At 22:47 -0800 3/17/03, Daren Cotter wrote: 
This seems to be doing the same thing as the
previously mentioned query...simply listing all
mailing IDs, along with the # of members it was sent
to. I've included both queries with their results
below.

mysql> SELECT COUNT(member_id), COUNT(mailing_id) FROM
member_mailings GROUP BY mailing_id;
+------------------+-------------------+
| COUNT(member_id) | COUNT(mailing_id) |
+------------------+-------------------+
|            10000 |             10000 |
|            25000 |             25000 |
|            10000 |             10000 |
|            25000 |             25000 |
|            53855 |             53855 |
|            53897 |             53897 |
|            53247 |             53247 |
|            15000 |             15000 |
|            10000 |             10000 |
|            10000 |             10000 |
|           140901 |            140901 |
|            10000 |             10000 |
+------------------+-------------------+
12 rows in set (0.57 sec)

mysql> select mailing_id, count(*) from
member_mailings group by mailing_id;
+------------+----------+
| mailing_id | count(*) |
+------------+----------+
|          1 |    10000 |
|          2 |    25000 |
|          3 |    10000 |
|          4 |    25000 |
|          6 |    53855 |
|          7 |    53897 |
|          8 |    53247 |
|         11 |    15000 |
|         12 |    10000 |
|         13 |    10000 |
|         15 |   140901 |
|         16 |    10000 |
+------------+----------+
12 rows in set (0.56 sec)

--- Zak Greant <[EMAIL PROTECTED]> wrote: 
 On Mon, Mar 17, 2003 at 09:52:44PM -0800, Daren
 Cotter wrote:
 > Jeff,
 >
 > That query simply gives me each mailing ID, along
 with
 > the # of members associated with that mailing ID.
 >
 > What I NEED is to return the # of mailings sent to
 a
 > member, and the number of members associated with
 that
 > number.
 >
 > I.e., if I do:
 >
 > SELECT count(*) FROM member_mailings WHERE
 member_id =
 > 1
 >
 > That returns the number of mailings for member 1,
 say
 > it's 25. That would be one tally in the "25" field
 for
 > # of mailings sent.
 >
 > It's tough to explain, so I'm thinking I won't be
 able
 > to accomplish it in one query?

Hello Daren,

   Assuming that your table looks something like
 this:

   +-----+-----------+---------+-----+
   | ... | member_id | mail_id | ... |
   +-----+-----------+---------+-----+
   | ... |         1 |       1 | ... |
   | ... |         2 |       1 | ... |
   | ... |         3 |       1 | ... |
   | ... |         1 |       2 | ... |
   | ... |         2 |       2 | ... |
   | ... |         3 |       3 | ... |
   +-----+-----------+---------+-----+


   Then this query should return the information that
 you desire:

   SELECT COUNT(member_id), COUNT(mail_id)
        FROM member_mailings
        GROUP BY mail_id;


   Cheers!
   --
   Zak Greant
   MySQL AB Community Advocate


--------------------------------------------------------------------- 
 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

--

Jeff Shapiro, Colorado Springs, CO, USA

At work I *have* to use a Windows machine, at home I *get* to use a Mac.

---------------------------------------------------------------------
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

Reply via email to