SQL groupby rollup vs union

Question

Sql question:

enter image description here competitor country Acme Corp USA GLOBEX USA Openmedia France K-bam USA Hatdrill UK Hexgreen Germany D-ranron France Faxla Spain the output should be

country competitors France 2 Germany 1 Spain 1 UK 1 USA 3 Total: 8 except using groupby with rollup, i am trying to solve it via "union" but turns out "order by is not functioning" (supposed to order by country name, but my output turns out to "order by competitors" ...) This is my code:

(select country, count(competitor) as competitors
    from table
    group by 1
    order by 1 
    )
    union all
    (select "Total:" as country, count(*) as competitors from table);

Any help would be appreciated! Thank you!

Answer 1

If you want the result ordered, you need an order by after the union:

(select country, count(competitor) as competitors
 from table
 group by 1
) union all
(select 'Total:' as country, count(*) as competitors
 from table
)
order by (country = 'Total:') desc, country asc