Getting Percentage based on records SQL Server

Question

Greetings I have this table Evaluation.Grade with these records:

Name Percentage
---------------
B       40
C       20
A       20
D       10
A+      10

And I am storing my given data after assessment to this table Evaluation.Assessment :

Total Grade 
------------
117   A+
2     D 

What I am trying to achieve here is getting what percentage of which grade I have stored so based on that i create a limitation back within my code. Something like this:

Grade Percentage
------------
A+    50
D     50
A     0
B     0
C     0

But right now using this query I get something like this:

Select 
    aa.Grade, (Count(aa.Grade) * 100 / 
                    (Select Count(*) 
                     From Evaluation.Assessment 
                     Where SupervisorId = '00000000-0000-0000-0000-000000000000')) as Percentage
From 
    Evaluation.Assessment aa
Where
    aa.SupervisorId = '00000000-0000-0000-0000-000000000000'
Group By 
    Grade


 Grade Percentage
 ----------------
 A+      50
 D       50

Answer 1

If I understood your requirement right, your can achieve this easily with windowed aggregation (i.e. using OVER)

CREATE TABLE grade 
  ( 
     NAME       VARCHAR(10), 
     percentage INT 
  ); 

CREATE TABLE assessment 
  ( 
     grade VARCHAR(10), 
     total INT 
  ); 

INSERT INTO grade 
VALUES ('B', 40), ('C', 20), ('A', 20), ('D', 10), ('A+', 10); 

INSERT INTO assessment 
VALUES ('A+', 117), ('D', 2), ('A', 65);

SELECT g.NAME                                     AS grade, 
       Count(a.total) 
         OVER( 
           partition BY a.grade) * 100 / Count(a.total) 
                                           OVER() AS percentage 
FROM   grade g 
       LEFT JOIN assessment a 
              ON g.NAME = a.grade 
ORDER  BY percentage DESC;

And this is how your result will look like:

Online executable of the query.