MySQL count how many records has user made

Question

I'm trying to count how many records has unique player [auth] made. So basically I'm trying to select lowest time on every map and count who has most entries left.

I got this far with a lot of googling.

SELECT
    auth,
    map,
    MIN(time) AS Record
FROM
    bhop.playertimes
WHERE track LIKE '0'
GROUP BY
    map
)

This successfully lists every maps top time and [auth] who made the record. What would be easiest way to sum the [auth] entries?

I would prefer this type answer query:

[auth] [records_made]

Answer 1

I think that you want:

select p.auth, count(*) no_records
from bhop.playertimes p
where 
    p.track = 0
    and p.time = (
        select min(p1.time) 
        from bhop.playertimes p1 
        where p1.map = p.map and p1.track = 0
    )

This gives you the number of maps per auth where they own the minimum time (which, as I understand, is your definition of an auth having made the record).

If you are running MySQL 8.0, you can get the same result with window function rank():

select auth, count(*) no_records
from (
    select auth, rank() over(partition by map order by time) rn
    from bhop.playertimes
    where track = 0
) p
group by auth