1

I realize there are several questions similar to this one already out there (here are three), but I've read through many of them and can't align the answers there with my particular situation (i.e., I'm a braindead n00b).

MY ULTIMATE GOAL

To create a list of the "most popular" groups, with the most popular at the top. Popularity is determined by the number of members. Each group has a count of members and quotes; I'd like to run a single query to get both counts.

TABLE STRUCTURE

group_members

+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| member_id          | int(10)          | NO   | PRI | None    |                |
| group_id           | int(10)          | NO   | PRI | None    |                |
+--------------------+------------------+------+-----+---------+----------------+

group_quotes

+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| quote_id           | int(10)          | NO   | PRI | None    |                |
| group_id           | int(10)          | NO   | PRI | None    |                |
+--------------------+------------------+------+-----+---------+----------------+

group_data

+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| entry_id           | int(10) unsigned | NO   | PRI | None    | auto_increment |
| title              | varchar(100)     | NO   |     | None    |                |
| url_title          | varchar(75)      | No   |     | None    |                |
+--------------------+------------------+------+-----+---------+----------------+

CURRENT (BROKEN) QUERY

I've tried the following (and close variations of it), but the result is a multiple of the number of entries in each table:

SELECT group_quotes.group_id, COUNT(group_quotes.group_id) AS numquotes, COUNT(group_members.group_id) AS nummembers
FROM group_quotes, group_members
GROUP BY group_quotes.group_id
ORDER BY numquotes
DESC LIMIT 5

...within the query loop, output the group's title, etc...

For example: when I last ran this query, there were 7 entries in the group_members table and 3 instances of group_id #10 in the group_quotes table; I got 21 for both the member count and quote count for group #10 (the correct numbers should be 2 and 3).

Would anyone mind taking a look and letting me know where I'm going wrong?

Community
  • 1
  • 1
Ryan Burney
  • 557
  • 7
  • 21

3 Answers3

2

UPDATED

Something like this should work:

SELECT    `group_data`.`id`,
          `group_data`.`title`,
          `group_data`.`url_title`,
          COUNT(DISTINCT `group_members`.`id`) AS  AS `group_members_count`, 
          COUNT(DISTINCT `group_quotes`.`id`) AS `group_quotes_count`
FROM      `group_data`
LEFT JOIN `group_members`
ON        `group_members`.`group_id`
LEFT JOIN `group_quotes`
ON        `group_quotes`.`group_id`
GROUP BY  `group_data`.`id`
ORDER BY  COUNT(`group_quotes`.`id`) DESC;
Francois Deschenes
  • 24,816
  • 4
  • 64
  • 61
  • I tried yours but could not get it to display the correct counts. Thank you for the great effort! – Ryan Burney Jun 16 '11 at 03:48
  • @Ryan - I've updated the query (I added DISTINCT) to only count distinct rows. I had a look at the other query and it seems to be counting the only one of the 2 tables (i.e. members or quotes) unlike my query above. Give this one and try and I trust you'll find that it works properly. Let me know. – Francois Deschenes Jun 16 '11 at 07:36
1

I may be missing the question, but I would think a union would do what is needed

select group_id, count( member_id ), 'member' from group_members group by group_id
union all
select group_id, count( quote_id), 'quote' from group_quotes group by group_id 
order by 2
Nick Gorham
  • 1,118
  • 8
  • 9
1

This may work well for you, also:

SELECT gd.entry_id, c1.c, c2.c
FROM group_data gd
LEFT JOIN 
(SELECT gq.group_id group_id, COUNT(*) c
FROM group_quotes gq
GROUP BY gq.group_id) c1
ON c1.group_id = gd.entry_id
LEFT JOIN
(SELECT gm.group_id group_id, COUNT(*) c
FROM group_members gm
GROUP BY gm.group_id) c2
ON c2.group_id = gd.entry_id
user470714
  • 2,858
  • 1
  • 28
  • 34
  • Why use subqueries when you can join the tables like I suggested in my example? – Francois Deschenes Jun 15 '11 at 00:27
  • @Francois Deschenes: No reason. Two ways to skin same cat. Although FWIW, testing your approach on my machine was much slower than subqueries. Somewhat surprising since subqueries generally introduce more bottlenecks than alleviate them. – user470714 Jun 15 '11 at 00:43
  • I'm quite surprised it's slower. Did you have indexes on the "id" fields? MySQL is extremely efficient at joining tables. You you use subqueries, you're essentially doing 2+ queries instead of one which should be slower, not faster. – Francois Deschenes Jun 15 '11 at 00:50
  • @Francois Deschenes: Indeed, I'm running an equivalent query in my currently existing schema, so I don't have to create a bunch of tables. There are some indexes on the tables I'm using. It could be something like that, or I could be rewriting your query incorrectly. In any case, OP should try both methods and see what works best for him ... but it appears OP won't even accept either of our answers! – user470714 Jun 15 '11 at 15:46
  • thank you for the answer; I appreciate it! I have to admit that your shorthand confused me a bit; my knowledge of complex SQL queries runs out about here. Nonetheless I tried your approach but could not get it to work as expected. (p.s. My email notifications don't seem to be working...thank you all for your quick responses). – Ryan Burney Jun 16 '11 at 03:49