Calculate percentages by group using sql

Question

I have a table that cotains the id of the students, the course name and the course level.

+----+--------+-------+   
| Id | Course | Level |   
+----+--------+-------+   
| 1  | A      | 1     |    
| 2  | A      | 1     |   
| 1  | B      | 1     |   
| 3  | B      | 1     |   
| 4  | C      | 2     |   
+----+--------+-------+  

From this, I want to know the percentage each course covers by level.

Like in the below table:

+-------+--------+----------------+  
| Level | Course | Count_by_level |  
+-------+--------+----------------+  
| 1     | A      | 50%            |  
| 1     | A      | 50%            |     
| 1     | B      | 50%            |   
| 1     | B      | 50%            |   
| 2     | C      | 100%           |   
+-------+--------+----------------+    

How can I do this using SQL?

Answer 1

Just another option using the window functions (assuming 2012+)

Example

Select [Level]
      ,[Course]
      ,Pct = 100.0  / sum(1) over (partition by [Level],[Course])
  From YourTable

Returns

Level   Course  Pct
1       A       50.000000
1       A       50.000000
1       B       50.000000
1       B       50.000000
2       C       100.000000

Answer 2

SQL DEMO

SELECT S.[Id] , S.[Course], S.[Level], T.ctotal,
       100.0 / T.ctotal
FROM students S
JOIN ( SELECT [Course], COUNT(*) as ctotal
       FROM students
       GROUP BY [Course]
      ) T
  ON S.[Course] = T.[Course]

OUTPUT

| Id | Course | Level | ctotal |     |
|----|--------|-------|--------|-----|
|  1 |      A |     1 |      2 |  50 |
|  2 |      A |     1 |      2 |  50 |
|  1 |      B |     1 |      2 |  50 |
|  3 |      B |     1 |      2 |  50 |
|  4 |      C |     2 |      1 | 100 |