Sports - Part 2

        

Write an SQL query to fetch all the occurrences of each sports from the Sports table and sort the occurrences in ascending order.

Input :-

Sports table :-
ID is the Primary Key column for the Persons table.

Name	Sports
Roopa	Throwball
Deepa	Cricket
Harish	Football
Aarav	Cricket
Aarya	Football
Anshuka	Throwball

Output :-

There are a total of 2 cricket players.

There are a total of 2 football players.

There are a total of 2 throwball players.

Query :-

select concat('There are a total of ',count(Sports),' ',lower(Sports),' players.'
from Sports
group by Sports
order by count(sports),sports;

Explanation : - 

The provided query is written in SQL and is used to retrieve information about the number of players in each sport from a table called "Sports".

• SELECT :- The query starts with the SELECT keyword, indicating that we want to retrieve specific information from the table.
• CONCAT :- The CONCAT function is used to combine multiple strings together. In this case, it is used to construct the output message.
• 'There are a total of ' :- This is a static string that will be included in the final output message.
• COUNT(Sports) :- This is an aggregate function that counts the number of rows (players) in each group (sport). It counts the occurrences of the "Sports" column.
• '  ' :- This is a space character, which will be included in the final output message to separate different parts.
• LOWER(Sports) :- The LOWER function is used to convert the "Sports" column value to lowercase. This is done to make the output message consistent and more readable.
• ' players.' :- This is another static string that will be included in the final output message.
• FROM Sports :- Specifies the table from which the data will be retrieved, in this case, the "Sports" table.
• GROUP BY Sports :- This clause groups the rows in the table by the values in the "Sports" column. This means that the COUNT function will calculate the number of players for each distinct sport.
• ORDER BY count(sports), sports :- This clause specifies the sorting order of the result set. It sorts the groups (sports) based on the count of players in ascending order and, within each count, it sorts the sports alphabetically.