SQL to return rows even if there is no data

Question

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.

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

This works perfectly and returns...

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

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.

Is there a way of returning data even if the grades have not been awarded or submitted yet eg

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

I wanted to graph this information and want to know if a Grade has not been awarded etc.

Thanks for any help.

There are multiple possible approaches, and I'm a bad person for not writing out a proper answer. With that out of the way: try adding WITH ROLLUP to your query and see if you can't filter that result down to what you want. — Jeroen Mostert
– Jeroen Mostert, Commented Oct 16, 2014 at 13:09

Paul Lucaciu · Accepted Answer · 2014-10-29 10:11:33Z

1

One way to achieve the above result is

WITH Results(FileYear,  AssessmentCode   ,Grade,   GradePerc)
AS
(
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
),
MyGrades(Grade)
AS
(
SELECT
    'A'
UNION ALL
SELECT
    'B'
UNION ALL
SELECT
    'C'
UNION ALL
SELECT
    'D'
UNION ALL
SELECT
    'E'
),
FinalResults(FileYear, AssessmentCode, Grade, GradePerc)
AS
(
    SELECT
        FileYear,
        AssessmentCode,
        CASE WHEN Results.Grade IS NULL THEN MyGrades.Grade ELSE Results.Grade END AS Grade,
        CASE WHEN Results.Grade IS NULL THEN 0 ELSE Results.GradePerc END AS GradePerc
    FROM
        Results 
        LEFT JOIN MyGrades
            ON Results.Grade IS NULL
    UNION ALL
    SELECT
        FileYear,
        AssessmentCode,
        MyGrades.Grade,
        0 AS GradePerc
    FROM
        MyGrades
        LEFT JOIN Results
            ON Results.Grade != MyGrades.Grade
)

SELECT
    FileYear,
    AssessmentCode,
    Grade,
    MAX(GradePerc) AS GradePerc
FROM
    FinalResults
GROUP BY
    FileYear,
    AssessmentCode,
    Grade
ORDER BY 
    AssessmentCode
    ;

edited Oct 29, 2014 at 10:11

answered Oct 16, 2014 at 13:47

Paul Lucaciu

1343 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Barrelnc Over a year ago

Thanks Paul, for your help - works perfectly. I had to get rid of the Order by AssessmentCode statement which it didn't like. Once commented out, it works well.

Barrelnc Over a year ago

Oops - jumped the gun a bit. For subjects with no grades entered, it works fine BUT for those where grades are entered it does the percentages for the entered grades but then does not also add a zero for the grades not entered. For example 11CPSSDD above has no rows for D and E still... Any thoughts?

Barrelnc Over a year ago

Hey Paul, any thoughts on trying to get the above to work correctly?

Paul Lucaciu Over a year ago

Hi Barrelnc, I will modify the query to give the correct results and then I will come back to you.

Paul Lucaciu Over a year ago

Hi Barrelnc, where you able to use the above query ?

|

DeadZone · Accepted Answer · 2014-10-16 14:05:13Z

If you can manipulate the database objects...

I don't know what your table structure looks like, but I would recommend that you make a table that stores your possible grades (A through E) and make vStudentReportsSemesterResults.AssessResultsResult a foreign-key column. (Since this looks like a view and not a table, you'd want to do this with the underlying table.) Now you can outer join to your new table and get results for every value in that table. As a bonus, if you ever change from "E" to "F" or if the grading system changes, you wouldn't need to adjust the query, since nothing is hard-coded in it.

If you can't manipulate the database objects...

You could make this query part of a stored procedure (or script) and create a temporary table (or local table variable) and join against that. But the concept is still the same.

Collectives™ on Stack Overflow

SQL to return rows even if there is no data

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related