Q:

sql : advanced sql issue

I have created the following table :
INSERT INTO ForgeRock
    ([productName], [description],grade)
VALUES
    ('OpenIDM', 'Platform for building enterprise provisioning solutions',10),
    ('OpenAM', 'Full-featured access management',20),
    ('OpenDJ', 'Robust LDAP server for Java',30),
    ('OpenDJ', 'Robust LDAP server for Java',40),
    ('OpenDJ', 'Robust LDAP server for Java',50),
    ('OpenDJ', 'Robust LDAP server for Java',60),
    ('OpenDJ', 'Robust LDAP server for Java',70);

I expect to select result set such as :
1                   90-100
3                   80-90
2                   70-80
.
.
.
3                   10-20
3                   0-10

How can I achieve this? I've already created a fiddle for you to test. 

A:

SELECT  SUM(studenttotal) ,
       grade
FROM    ( SELECT    COUNT(productName) studenttotal ,
                CASE WHEN grade BETWEEN 91 AND 100 THEN '91-100'
                     WHEN grade BETWEEN 81 AND 90 THEN '81-90'
                     WHEN grade BETWEEN 71 AND 80 THEN '71-80'
                     WHEN grade BETWEEN 61 AND 70 THEN '61-70'
                     WHEN grade BETWEEN 51 AND 60 THEN '51-60'
                     WHEN grade BETWEEN 41 AND 50 THEN '41-50'
                     WHEN grade BETWEEN 31 AND 40 THEN '31-40'
                     WHEN grade BETWEEN 21 AND 30 THEN '21-30'
                     WHEN grade BETWEEN 11 AND 20 THEN '11-20'
                     WHEN grade BETWEEN 1 AND 10 THEN '1-10'
                END AS grade
      FROM      ForgeRock
      GROUP BY  grade
    ) a
GROUP BY grade
ORDER BY grade DESC

