CODEWITHSUNDEEP
mysql
Lilly
Lilly

Reputation: 69

Retrieve information SELECET from tables

I want to retrieve information, specifically the names and the total number of unique activities of an athelete (which i've renamed "sports Variety". I want to rank first the athlete who does more activities, but I'm not sure how to approach this.

Table 1 Table 2

I also know I should use DISTINCT somewhere, but not sure where to put it. I also don't know how to sort by unique/most activities. 

SELECT athlete.name, COUNT(athlete.name) AS 'Sports Variety' FROM athlete 
INNER JOIN athlete ON athlete.id = training_session.activity GROUP BY 
training_session.activity HAVING COUNT(athlete) > 0

I expect it to be 6 rows to be returned.

Upvotes: 0

Views: 30

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

assuming you have two table one name athlete with athlete name and one name training_session for the activities related to each athletes 

SELECT athlete.name, COUNT(*) AS 'Sports Variety' 
FROM athlete 
INNER JOIN training_session ON athlete.id = training_session.athlete_id 
GROUP BY athlete.name

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

If you want to find the athlete having the most activities, you could try a LIMIT query:

SELECT
    a.id,
    a.name,
    COUNT(*) AS num_activities
FROM athlete a
INNER JOIN training_session ts
    ON a.id = ts.athelete_id
GROUP BY
    a.id,
    a.name
ORDER BY
    COUNT(*) DESC
LIMIT 1;

I see a handful of syntax problems with your current query, such as the second table name is the same as the first (not what you want here). Note that my query doesn't handle the possibility that two or more athletes might happen to have the same amount of activity. To resolve that, we could either find something else to rank, or restructure the query.

Upvotes: 1

Related Questions