SQL Server Month Totals

Question

SQL Server newbie

The following query returns SRA by Student and month only if there is a record for a student in Discipline table. I need a query to return all students and month totals even if there is no record for student in Discipline table. Any direction appreciated

SELECT  TOP 100 PERCENT MONTH(dbo.Discipline.DisciplineDate) AS [Month], dbo.Discipline.StuId, dbo.Stu.Lastname + ',' + dbo.Stu.FirstName AS Student, 
    SUM(CASE WHEN Discipline.SRA = 1 THEN 1 END) AS [Acad Suspension], SUM(CASE WHEN Discipline.SRA = 2 THEN 1 END) AS Conduct, 
    SUM(CASE WHEN Discipline.SRA = 3 THEN 1 END) AS Disrespect, SUM(CASE WHEN Discipline.SRA = 4 THEN 1 END) AS [S.R.A], 
    SUM(CASE WHEN Discipline.SRA = 5 THEN 1 END) AS Suspension, SUM(CASE WHEN Discipline.SRA = 6 THEN 1 END) AS Tone
FROM dbo.Discipline INNER JOIN
     dbo.Stu ON dbo.Discipline.StuId = dbo.Stu.StuId
GROUP BY dbo.Discipline.StuId, dbo.Stu.Lastname, dbo.Stu.FirstName, MONTH(dbo.Discipline.DisciplineDate)
ORDER BY Student

Answer 1

generate a series of months, join discipline to that.

Answer 2

You need to change the INNER JOIN onto dbo.Stu to a LEFT JOIN:

   SELECT MONTH(d.disciplinedate) AS [Month], 
          d.StuId, 
          s.Lastname + ',' + s.FirstName AS Student, 
          SUM(CASE WHEN d.SRA = 1 THEN 1 END) AS [Acad Suspension], 
          SUM(CASE WHEN d.SRA = 2 THEN 1 END) AS Conduct, 
          SUM(CASE WHEN d.SRA = 3 THEN 1 END) AS Disrespect, 
          SUM(CASE WHEN d.SRA = 4 THEN 1 END) AS [S.R.A], 
          SUM(CASE WHEN d.SRA = 5 THEN 1 END) AS Suspension, 
          SUM(CASE WHEN d.SRA = 6 THEN 1 END) AS Tone
     FROM dbo.Discipline d
LEFT JOIN dbo.Stu s ON s.stuid = d.stuid
 GROUP BY d.StuId, s.Lastname, s.FirstName, MONTH(d.DisciplineDate)
 ORDER BY Student

The LEFT JOIN means that whatever table you're LEFT JOINing to might not have records to support the JOIN, but you'll still get records from the base table (dbo.Discipline).

I used table aliases - d and s. Less to type when you need to specify references.