SQL Server - Order by clause disturbs the Grouping in the Query.

Asked By Charvi Sanghavi on 21-Aug-16 02:08 PM

I have a table that looks like this :


Region   Country   Earnings

Americas    Canada   10000

Americas    Mexico    20000

Americas    USA     50000

Asia-Pac   China    70000

Asia-Pac   Japan    40000

  

I need a report that groups the results based on the Region and then the Country as well as orders by the Earnings within the Region in a descending manner. I am using the below query :


Select Region, Country, SUM(Earnings) from MyTable

Group By Region, Country

Order By SUM(Earnings) DESC


However, the result set that I get looks like the below :


Region   Country   Earnings

Asia-Pac   China    70000

Americas    USA    50000

Asia-Pac   Japan    40000

Americas    Mexico    20000

Americas    Canada   10000


i.e. I get the result set ordered by Earnings in a descending manner but the grouping gets disturbed.

Any ideas?

Robbe Morris replied to Charvi Sanghavi on 21-Aug-16 02:07 PM
order by Country ASC, Region ASC, Sum(Earnings) DESC