question

Try this: DECLARE @LearnerTable TABLE ( Learner VARCHAR(20) , GLH INT ) INSERT INTO @LearnerTable VALUES ( 'John', 100 ), ( 'Harry', 200 ), ( 'Fred', 300 ), ( 'Ted', 350 ); WITH LearnerSumGLH AS ( SELECT Learner , SUM(GLH) AS SumGLH FROM @LearnerTable GROUP BY Learner ) SELECT CASE WHEN SumGLH < 300 THEN 'Under 300' WHEN SumGLH >= 350 THEN '350' ELSE '300-350' END AS GLH , COUNT(*) AS NumLearners FROM LearnerSumGLH GROUP BY CASE WHEN SumGLH < 300 THEN 'Under 300' WHEN SumGLH >= 350 THEN '350' ELSE '300-350' END However, if you have to have the data in that order, then that's a little more complicated: DECLARE @LearnerTable TABLE ( Learner VARCHAR(20) , GLH INT ) INSERT INTO @LearnerTable VALUES ( 'John', 100 ), ( 'Harry', 200 ), ( 'Fred', 300 ), ( 'Ted', 350 ); WITH LearnerSumGLH AS ( SELECT Learner , CASE WHEN SUM(GLH) < 300 THEN 'Under 300' WHEN SUM(GLH) >= 350 THEN '350+' ELSE '300-350' END AS SumGLH , CASE WHEN SUM(GLH) < 300 THEN 1 WHEN SUM(GLH) >= 350 THEN 3 ELSE 2 END AS GLHOrder FROM @LearnerTable GROUP BY Learner ) SELECT SumGLH AS GLH , COUNT(*) AS NumLearners FROM LearnerSumGLH GROUP BY SumGLH , GLHOrder ORDER BY GLHOrder In both answers, what we're doing is using a [WITH statement to create a CTE (Common Table Expression)][1]. This allows us to create an intermediate pseudo-table which holds the results of the initial select statement (in my case, from a table variable, in your case from a base table). This particular SELECT statement differs from yours in that I'm transforming the data into something that is more obviously related to the output you require - replacing GLH values with your grade band labels, for example - and (in the second example) also setting up a column for sorting based on the same cases. Note that the second example brings the data transformation up into the CTE declaration to make the final SELECT statement easier to read. I could also have done this with the first example... [1]: http://msdn.microsoft.com/en-us/library/ms175972.aspx