list and count from same table

[showman13]

Hi All...

I have a simple table with 2 fields in it...
mem_id and ref_id

For each mem_id in the table, there is an undetermined number of ref_id records...

How do I get a listing of the unique mem_ids along with a number that represents the number of records with that mem_id and different ref_ids?

Seems it should be pretty simple, but my eyes are crossing looking at it.

this is the latest test query, still with no luck

SELECT a.mem_id, count(b.ref_id) 
FROM ref_track as a, ref_track as b 
WHERE b.mem_id=a.mem_id 
GROUP BY a.mem_id 
ORDER BY a.mem_id

I always get a list of the 39,000+ records with 1 for a count for each of them...

Any help would be greatly appreciated.

thanks
Douglas

[smantscheff]

select distinct a.mem_id, 
(select count(b.ref_id) from ref_track b where b.mem_id=a.mem_id) as cnt
from ref_track a
order by mem_id

The downside is that the aggregate function is called for each row of ref_track.

[showman13]

Well, the downside isn't really an issue, because it is something that would seldom ever be used... But it is something that I just need now and may at some point in the future be used again..

BUT, I just ran that query, and got exactly the same result... 39,919 records returned with a cnt value of 1.

any other suggestions on how to get a count on ref ids for each mem_id?

Thanks in advance.

Douglas

[BitBlt]

I'm gonna have a whack at this. Give me a few minutes.

[BitBlt]

If you're looking for just a list of mem_id's and the number of ref_id's, use this:

select mem_id, count(distinct ref_id) 
from ref_track
group by mem_id
order by mem_id

If the above is not what you are looking for, and you want to see a combination of mem_id and ref_id, and a count of OTHER ref_id's for that mem_id, try this:

select a.mem_id, a.ref_id, 
(select count(b.ref_id) from ref_track b where b.mem_id = a.mem_id and b.ref_id <> a.ref_id) 
as 'CountOfRowsWithSameMemIdDifferentRefId'
from ref_track a
order by mem_id

If neither of these are what you need, can you explain further, or post what you expect your output to look like?

[smantscheff]

Before submitting, I tested my code on this test data:

DROP TABLE IF EXISTS `ref_track`;
CREATE TABLE `ref_track` (
  `mem_id` int(11) DEFAULT NULL,
  `ref_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ref_track` VALUES (1,1),(1,2),(1,3),(2,1),(2,2);

with this result:

select distinct a.mem_id, 
(select count(b.ref_id) from ref_track b where b.mem_id=a.mem_id) as cnt
from ref_track a
order by mem_id
+--------+------+
| mem_id | cnt  |
+--------+------+
|      1 |    3 |
|      2 |    2 |
+--------+------+

If that's not what you expect, show your test data.

[showman13]

BitBlt - Thank you also for your response...

the first solution gave me the same results... 39,919 mem_id records listed with a count of 1 for each of them

the second solution displayed the same records with a ref_id for each and the count of 0

[showman13]

Boy do I feel like an idiot...

the results are exactly what they should be because I presented it backwards in the question

there are 39,919 members and naturally each of them are only going to have 1 referrer...

What I needed to get is how many mem_id records are attributed to each distinct ref_id.

There can be many members to a single referrer, but only one referrer to each member

[BitBlt]

At least you have your solution. Just pick one of the offered examples (I like mine better, of course :-) although either would work) and swap ref_id and mem_id.

Look at it this way...you also got to see a couple of different methods for solving the same problem. And, we got to have some fun helping out. As the great Chick Hearn (late former announcer for the LA Lakers) used to say, "No harm, no foul, no blood, no ambulance!". Happy Coding!

[showman13]

you are absolutely correct... your first one worked perfectly...

    select ref_id, count(distinct mem_id)
    from ref_track
    group by ref_id
    order by ref_id

Thank you ALL for your help.

Douglas