Help: Sorting by Two Columns

1

I am fairly new to SQL and am trying to learn a few things on my own, so this may seem pretty basic. I still seemingly cant get it to work out properly. What I am tasked with doing is taking data of Test Scores and Test Names and sorting them by the most often taken test and then by the score. So it should have the most common tests taken at the top going from highest score to lowest score on that test. Then on to the second most common test, so on and so far.

Here is the code that thought would work: SELECT score, AssessmentType, count(AssessmentType) AS 'Count'
FROM AssessmentScore
GROUP BY AssessmentType
ORDER BY 'Count' DESC, score DESC

However, i am getting an error that is telling me "Msg 8120, Level 16, State 1, Line 1
Column 'AssessmentScore.score' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I understand this is telling me that the 'score' column is invalid because it needs to be in the Group by clause. But that still doesnt make sense why that is an error.

If you could help me out that would be awesome! Thanks!

2

Add score field to the "group by" section:

SELECT score, AssessmentType, count(AssessmentType) AS 'Count’
FROM AssessmentScore
GROUP BY score,AssessmentType
ORDER BY ‘Count’ DESC, score DESC

3

By doing that, it will group ALL of the different scores together. So it counts the amount of times a certain score happens. So i have a lot of 0's for scores so itll show say 'Count 50' for 0's in a column.
I don't want to group by the score. I want to sort by most taken test and THEN sort by score.

4

What version of SQL Server are you using?

5

Microsoft SQL Server Management Studio 17

6 
select score
      ,assessmenttype
      ,count(*) over(partition by assessmenttype) as [count]
  from assessmentscore
 order by [count] desc
         ,score desc
;

ps.: you find version by doing: select @@version

7

Works perfectly. Thank you so much!