mysqlcountmembers

I want to find out how many new members I have using MySQL for the registration year 2017

My columns are Year and AthleteID. Every time someone registers they keep the same AthleteID, so A query that returns all members from all years would look like the below:

|AthleteID | Year|
| 1234     | 2016|
| 1234     | 2017|
| 3243     | 2016|
| 0134     | 2015|
| 4567     | 2017|
| 5678     | 2017|
| 1234     | 2017|

I want to return just new member in 2017 so in the example above it would return only the below:

|AthleteID|| Year|
| 4567     | 2017|
| 5678     | 2017|
Solution

  • One way to do this is to get the minimum year for each athlete, and then filter the new users with a HAVING clause.

    Like so:

    SELECT AthleteID,
       min(YEAR)
FROM your_table
GROUP BY AthleteID
HAVING min(YEAR) = 2017