I have a problem with the following task from the platform Codesignal:

After some investigation, you’ve created a database containing a foreignCompetitors table, which has the following structure:

competitor: the name of the competitor;
country: the country in which the competitor is operating.
In your report, you need to include the number of competitors per country and an additional row at the bottom that contains a summary: ("Total:", total_number_of_competitors)

Given the foreignCompetitors table, compose the resulting table with two columns: country and competitors. The first column should contain the country name, and the second column should contain the number of competitors in this country. The table should be sorted by the country names in ascending order. In addition, it should have an extra row at the bottom with the summary, as described above.

Example

For the following table foreignCompetitors

my solution:

CREATE PROCEDURE solution()
BEGIN
    (SELECT country, COUNT(*) AS competitors 
    FROM foreignCompetitors
    GROUP BY country
    ORDER BY country)

    UNION
    
    SELECT 'Total:', COUNT(*) FROM foreignCompetitors;
    
END

But my output is:

The result of the countries is not sorted by their names.
I cannot understand why is that even though I try to sort them with ORDER BY.

>Solution :

You want a GROUP BY WITH ROLLUP here:

SELECT COALESCE(country, 'Total:') AS country, COUNT(*) AS competitors
FROM foreignCompetitors
GROUP BY country WITH ROLLUP
ORDER BY country;

If you want to stick with your union approach, then you need to introduce a computed column into the union query which places the total row at the bottom of the result set. Consider:

SELECT country, competitors
FROM
(
    SELECT country, COUNT(*) AS competitors, 1 AS pos
    FROM foreignCompetitors
    GROUP BY country
    UNION ALL    
    SELECT 'Total:', COUNT(*), 2
    FROM foreignCompetitors
) t
ORDER BY pos, country;