CODEWITHSUNDEEP
sqlsql-servert-sql
user224271
user224271

Reputation:

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.StuId, dbo.Stu.Lastname, dbo.Stu.FirstName, MONTH(dbo.Discipline.DisciplineDate)
ORDER BY Student

Upvotes: 0

Views: 207

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332521

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.

Upvotes: 1

just somebody
just somebody

Reputation: 19247

generate a series of months, join discipline to that.

Upvotes: 0

Related Questions