Philip Hendry's Blog

Conditional Aggregate Totals in SQL

Sep 11, 2009 • SQL Server, T-SQL, Tip • 1 min read

I’ve been working on a SQL Server Reporting Services report which had to display several counts on each row. Each row represented a project and each project could have several tasks that were either ordinary tasks or milestones and could also be escalated. The report need to show something like this :

Project Name Milestone Counts Task Count Escalated Task Count
1st Week 2nd Week 3rd Week >=4th Week Total 1st Week 2nd Week 3rd Week >=4th Week Total

As you can see a task initially has to break down into two major colours of milestone and ordinary task counts but then is broken down into counts in the 1st, 2nd, 3rd and greater than 4th week totals including the total itself.

Initially this seemed tricky when thinking in terms of using count() but there’s an incredibly simple technique that solves this but which isn’t immediately obvious. The Sum() aggregate function can contain an expression and in this example that expression could compare the task date against late week dates and return either a 1 or 0 :

select ProjectName,
   sum(case when IsMilestone = 1 
      and TaskDate < @LateDate 
      and Taskdate >@OneWeekLateDate               
      then 1 else 0 end) as Week1LateMilestoneCount
from Project
group by ProjectName

I’ve only included one example there but I think it’s enough to get the idea.

Post by: Philip Hendry