Order by clause disturbs the grouping in the query.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

C#

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?

What I have tried:

I have tried writing the query myself & its as below :

Select Region, Country, SUM(Earnings) from MyTable

Group By Region, Country

Order By SUM(Earnings) DESC

Posted 21-Aug-16 23:51pm

aakar

Updated 22-Aug-16 0:14am

Add a Solution

Comments

[no name] 22-Aug-16 5:56am

Your query does exactly what it should. You Group By Region _and_ Country and the table you Show does _only_ have entries of different Groups at the Moment.... add e.g another row for "Americas, Canada" with earning of 70'000 and you see everything is correct.

phil.o 22-Aug-16 6:02am

Since you have only one country occurrence for each region, the result you get is correct. You may try to add some more region/country duplicates with different earnings to have a more meaningful result.

[no name] 22-Aug-16 6:11am

I think you mean "for each Region" :)

phil.o 22-Aug-16 6:14am

Oops :)
Now corrected. Thanks for pointing it.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2016-08-22T00:14:00

Solution 1

Quote:
i.e. I get the result set ordered by Earnings in a descending manner but the grouping gets disturbed.
Any ideas?

Sure, it is what you asked for !

See the usage of "GROUP BY" and "ORDER BY"
SQL ORDER BY Keyword[^]

Posted 22-Aug-16 0:14am

Patrice T

OriginalGriff · Answer 2 · 2016-08-22T00:14:00

Solution 2

Try:

SQL

... ORDER BY Region ASC, SUM(Earnings) DESC

Posted 22-Aug-16 0:14am

OriginalGriff

Comments

aakar 23-Aug-16 4:14am

Thanks all for your suggestions! However, the report that I need should look like :

Region Country Earnings

Americas USA 50000

Americas Mexico 20000

Americas Canada 10000

Asia-Pac China 70000

Asia-Pac Japan 40000

OriginalGriff 23-Aug-16 4:22am

And that's what the two ORDER BY clauses do...