driz driz - 6 months ago 13
MySQL Question

Mysql count per distinct user with a join

currently i have two tables with some data. the first table has the following:

+----------------+-----------+
| name | member_id |
+----------------+-----------+
| Juice Box | 49432 |
| Rainsurge | 49631 |
| spiderpigrider | 50482 |
+----------------+-----------+


The second table has the following:

+------------+-----------+
| recruit_id | bin(refs) |
+------------+-----------+
| 49432 | 1 |
| 49631 | 1 |
| 49432 | 1 |
| 49631 | 1 |
| 49432 | 1 |
| 49631 | 1 |
| 49432 | 1 |
| 49631 | 1 |
| 49432 | 1 |
| 49631 | 1 |
+------------+-----------+


I would like to return the name, total refs and member_id/recruit_id like so (listing only users with at least 1 ref)

+------------+-----------+------------+
| recruit_id | name | total_refs |
+------------+-----------+------------+
| 49631 | Rainsurge | 5 |
| 49432 | Juice Box | 5 |
+------------+-----------+------------+

select r.recruit_id,bin(r.refs),ipb.name from refs as r
inner join syndicate_ipb.core_members as ipb on ipb.member_id=r.recruit_id;


this returned my data but obviously without a total count and repeated names/ids

select r.recruit_id,count(bin(r.refs)),ipb.name from refs as r
inner join syndicate_ipb.core_members as ipb on ipb.member_id=r.recruit_id;


this returned data with the total count of everyone but only one id/name

+------------+--------------------+-----------+
| recruit_id | count(bin(r.refs)) | name |
+------------+--------------------+-----------+
| 49432 | 10 | Juice Box |
+------------+--------------------+-----------+


this returns the data but again without a count

select distinct r.recruit_id,bin(r.refs),ipb.name from refs as r
inner join syndicate_ipb.core_members as ipb on ipb.member_id=r.recruit_id;

+------------+-------------+-----------+
| recruit_id | bin(r.refs) | name |
+------------+-------------+-----------+
| 49432 | 1 | Juice Box |
| 49631 | 1 | Rainsurge |
+------------+-------------+-----------+


Any help or guidance is greatly appreciated. I feel like i'm close here but just not competent enough with SQL to get it. thanks!

Answer

You were almost there. You just missed the GROUP BY clause at the end.

Query:

SELECT
    r.recruit_id,
    count(bin(r.refs)),
    ipb.name
FROM refs AS r
INNER JOIN syndicate_ipb.core_members AS ipb 
ON ipb.member_id = r.recruit_id
GROUP BY r.recruit_id;

Note:

If bin(refs) column always contains value 1 then actually you don't need to keep that column. In that case you can use count(*) or count(r.recruit_id) to get the count.

And if bin(refs) column contains any value then count will not give you the right answer. In that case you need to use sum like Sum( bin(refs)).