title: SQL to return rows even if there is no data
body: <p>I have some students are are awarded grades A to E at the end of the year. I find the percentage of students who have been awarded grades grouped by AssessmentCode using the following SQL.</p>
  
  <pre><code>SELECT '2014' as FileYear,
      AssessmentCode, 
      AssessResultsResult as Grade,
      cast(count(*)*100.0/sum(count(*)) over (partition by assessmentcode) as decimal(5,2))as GradePerc
  FROM   vStudentReportsSemesterResults
  WHERE  (FileYear = 2014) 
     AND (FileSemester = 2) 
     AND (AssessmentCode like '11%') 
     AND (AssessAreaHdgAbbrev2 = 'FinalGrade')
  
     group by AssessmentCode,
       AssessResultsResult
  
  order by assessmentcode
  </code></pre>
  
  <p>This works perfectly and returns...</p>
  
  <pre><code>FileYear  AssessmentCode   Grade   GradePerc
    2014       11CPSIPT        NULL     100
    2014       11CPSSDD        A        11.76
    2014       11CPSSDD        B        47.06
    2014       11CPSSDD        C        41.18
    2014       11DRADRA        NULL     100
    2014       11GEOGEO        A        6.25
    2014       11GEOGEO        B        56.25
    2014       11GEOGEO        C        28.13
    2014       11GEOGEO        D        9.38
    2014       11HISANC        NULL     100
  </code></pre>
  
  <p>Note that some assessment codes have not submitted grades as yet (NULL in the Grade column) and others have submitted grades but there are no students who have been awarded a D or an E, for example.</p>
  
  <p>Is there a way of returning data even if the grades have not been awarded or submitted yet eg </p>
  
  <pre><code>FileYear  AssessmentCode   Grade   GradePerc
    2014       11CPSIPT        A        0
    2014       11CPSIPT        B        0
    2014       11CPSIPT        C        0
    2014       11CPSIPT        D        0
    2014       11CPSIPT        E        0
    2014       11CPSSDD        A        11.76
    2014       11CPSSDD        B        47.06
    2014       11CPSSDD        C        41.18
    2014       11CPSSDD        D        0
    2014       11CPSSDD        E        0
    2014       11DRADRA        A        0
    2014       11DRADRA        B        0
    2014       11DRADRA        C        0
    2014       11DRADRA        D        0
    2014       11DRADRA        E        0
    2014       11GEOGEO        A        6.25
    2014       11GEOGEO        B        56.25
    2014       11GEOGEO        C        28.13
    2014       11GEOGEO        D        9.38
    2014       11GEOGEO        E        0
  </code></pre>
  
  <p>I wanted to graph this information and want to know if a Grade has not been awarded etc.</p>
  
  <p>Thanks for any help.</p>