CODEWITHSUNDEEP
mysqldistinct
buzibuzi
buzibuzi

Reputation: 734

Count distinct records from child table for each user in MYSQL

I have a competition which counts how many species each user has collected. this is managed by 3 tables:

  1. a parent table called "sub" with collection,each collection is unique, has an id and is associated to a user id. 
    
+----+---------+
| id | user_id |
+----+---------+
|  1 |       1 |
|  2 |      10 |
|  3 |       1 |
|  4 |       3 |
|  5 |       1 |
|  6 |      10 |
+----+---------+
  2. the child table called "sub_items" contains multiple unique records of the specs and is related to the parent table by the sub id to id.(each sub can have multiple records of specs)

+----+--------+---------+--+
| id | sub_id | spec_id |  |
+----+--------+---------+--+
|  1 |      1 |    1000 |  |
|  2 |      1 |    1003 |  |
|  3 |      1 |    2520 |  |
|  4 |      2 |    7600 |  |
|  5 |      2 |    1000 |  |
|  6 |      3 |      15 |  |
+----+--------+---------+--+

  1. a user table with associated user_id

+--------+-------+--+
| usename | name    |
+---------+-------+--+
|      1 | David    |
|     10 | Ruth     |
|      3 | Rick     |
+--------+-------+--+

i need to list the users with the most unique specs collected in a decsending order. output expected: David has a total of 2 unique specs.Ruth has a total of 2 unique specs. 




+--------+---------+
| id     | total   |
+----+-------------+
|  David |    2    |
|  Ruth  |    2    |
|  Rick  |    2    |
+----+-------------+
so far i have this,it produces a result. but its not accurate, it counts the total records. im probably missing a DISTINCT somewhere in the sub-query.

SELECT s.id, s.user_id,u.name, sum(t.count) as total
FROM sub s
LEFT JOIN (
    SELECT id, sub_id, count(id) as count FROM sub_items GROUP BY sub_id
 ) t ON t.sub_id = s.id
 LEFT JOIN user u ON u.username = s.user_id
 GROUP BY user_id
 ORDER BY total DESC

i have looked at this solution, but it doesn't consider the unique aspect

Upvotes: 0

Views: 551

Answers (2)

buzibuzi
buzibuzi

Reputation: 734

this worked for me, i have to add the 

COUNT(distinct spec_id)

to the sub-query 

SELECT s.id, s.user_id,u.name, sum(t.count) as total
FROM sub s
LEFT JOIN (
    SELECT sub_id, COUNT(distinct spec_id) as count FROM sub_items group by sub_id
 ) t ON t.sub_id = s.id
 LEFT JOIN user u ON u.username = s.user_id
 GROUP BY user_id
 ORDER BY total DESC

Upvotes: 0

JNevill
JNevill

Reputation: 50034

You'll first have to get the max "score" for all the users like:

    SELECT count(DISTINCT si.id) as total
    FROM sub INNER JOIN sub_items si ON sub.id = su.sub_id
    GROUP BY sub.user_id
    ORDER BY total DESC
    LIMIT 1

Then you can use that to restrict your query to users that share that max score:

SELECT u.name, count(DISTINCT si.id) as total
FROM
    user u
    INNER JOIN sub ON u.usename = sub.user_id
    INNER JOIN sub_items si ON sub.id = su.sub_id
GROUP BY u.name
HAVING total =
    (
        SELECT count(DISTINCT si.id) as total
        FROM sub INNER JOIN sub_items si ON sub.id = su.sub_id
        GROUP BY sub.user_id
        ORDER BY total DESC
        LIMIT 1
    )

Upvotes: 1

Related Questions