SQL Server Month Totals

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.S开发者_如何学GotuId, dbo.Stu.Lastname, dbo.Stu.FirstName, MONTH(dbo.Discipline.DisciplineDate)
ORDER BY Student

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.

generate a series of months, join discipline to that.