why are you using distinct(ref1)?
also count(*) is ambiguous in this case. should be count(a.*).
you have a ref1 column in both tables, but you don't join them on it. that's
kind of confusing but won't cause the problem. member_id is the primary key
on the members table, right? Otherwise you could get bad data.
I think eliminating the distinct will solve your problem:
select a.id, count(a.*) as nrows, b.field1, b.field2, etc.
from a, b
where a.id=b.id
group by a.id, b.field1, b.field2, etc.
order by nrows desc
limit 100
note you can put a.id alone in the group by, but this is not ANSI sql, so it
might be best to avoid this mysql-specific behavior.
braxton
-----Original Message-----
From: Daren Cotter [mailto:[EMAIL PROTECTED]]
Sent: Thursday, March 22, 2001 5:31 PM
To: [EMAIL PROTECTED]
Subject: Referer Count
I have a table, which keeps track of member information (including which
member referred the member). To get a count of the # of referrals for member
25, my query is:
SELECT COUNT(*) FROM members WHERE ref1 = 25;
To get a list of the top referers and the # of referrals they have, my query
is:
SELECT DISTINCT(ref1) AS member_id, COUNT(*) AS count FROM members GROUP BY
ref1 ORDER BY count DESC LIMIT 100
However, what I need, is a list of the top referers, along with their member
information...name, email, password, etc. I tried using the following query,
as I read about it in the MySQL manual, but it doesn't work:
SELECT DISTINCT(a.ref1) AS member_id, count(*) AS count, b.password,
concat(UCASE(SUBSTRING(b.first_name,1,1)),
LCASE(SUBSTRING(b.first_name,2,LENGTH(b.first_name)))) AS name, b.email,
b.html_mail, b.ref1, DATE_FORMAT(b.signup_date, '%b %e, %Y') AS signup_date
FROM members AS a, members AS b WHERE a.active_member = 'Y' AND a.ref1 =
b.member_id GROUP BY a.ref1 ORDER BY count DESC LIMIT 10
This gives me correct info for the distinct a.ref1 and count fields, and
produces data for the rest of the fields, but it is not actually that
member's data. Is this possible to do with one query? If I want to get the
top 100 referers' data, I don't want to do 100 separate queries. Please
help!
Thanks,
Daren Cotter
---------------------------------------------------------------------
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
