[RESOLVED] Need explanation of what this statement does?

[blakemckenna]

Can anyone tell me what this statement is doing? To me, it doesn't look like it's summing anything. I've never seen this kind of statement before.

SUM(case when isnull(old.field1, '') = isnull(new.field1, '') THEN 1 ELSE 0 END)

Thanks,

[jmcilhinney]

It's effectively counting the number of rows in which 'old.field1' and 'new.field1' contain the same value and it is treating NULL and an empty string as being equal. For each row, it basically replaces NULL with an empty string in the 'field1' column of both 'old' and 'new'. It then compares those two columns and returns 1 if they are equal and 0 if they are not. It then sums all those numeric results.

[FunkyDexter]

It's a way of doing a count of records which meet a criteria.

So this bit:-

case when isnull(old.field1, '') = isnull(new.field1, '') THEN 1 ELSE 0 END

…will return 1 when old.field1 = new.field1. The isnulls are probably there to make NULL = NULL (it usually doesn't, but that's a whole 'nother discussion) but they will have the side effect of allowing blank to equal null too. When those equalities are not met it will return zero.


The sum then adds all the zeros and ones together which, since zero doesn't have any effect on an addition, is the same as adding all the ones together.

The net effect is that you'll get a count of all the records where the case criteria is met.

edit>crossed with JM

[jmcilhinney]

edit>crossed with JM

If you didn't spend so much time pontificating and adding your fancy code tags, maybe you'd get in first.

[szlamany]

I use SUM(CASE...) all the time

This finds all the tasks for a given zone and counts those due in each of the upcoming 5 weeks.

Select @TaskCnt=IsNull(Sum(1),0)--, @TaskLatest=Max(TaskDate)
	,@TaskCntW1=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) Between 1 and 7 Then 1 Else 0 End),0)
	,@TaskCntW2=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) Between 8 and 14 Then 1 Else 0 End),0)
	,@TaskCntW3=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) Between 15 and 21 Then 1 Else 0 End),0)
	,@TaskCntW4=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) Between 22 and 28 Then 1 Else 0 End),0)
	,@TaskCntW5=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) Between 29 and 35 Then 1 Else 0 End),0)
	,@TaskCntOther=IsNull(Sum(Case When DateDiff(dd,GetDate(),TaskDate) not Between 1 and 35 Then 1 Else 0 End),0)
From Task_V Where TaskStatus not in ('Completed','Deleted') and Employee=@Staff--Zone=@Zone

This one gets the violation codes along with the count of FAILS, the TOTAL count and also the LATEST DATE

Select viosCode,f_text
	,sum(case When f_pass='Fail' Then 1 Else 0 End) "FailCount"
	,sum(1) "TotalCount"
	,Convert(varchar(10),max(f_date),101) "LatestDate"

This one does TASK STATISTICS by FACILITY - note that it's not a simple Then 1 Else 0 End like the others.

Select	 TV.Facility				"~wide~Facility"
	,FT.EdLevel				"~wide~Ed Level"
	,FT.NonPublic				"~wide~Non Public"
	,ST.Category				"~wide~Category"
	,ST.Statistic				"~wide~Statistic"
	,Sum(Case When DatePart(mm,TS.SDate)=7 Then TS.SCount Else 0 End)	">Jul"
	,Sum(Case When DatePart(mm,TS.SDate)=8 Then TS.SCount Else 0 End)	">Aug"
	,Sum(Case When DatePart(mm,TS.SDate)=9 Then TS.SCount Else 0 End)	">Sep"
	,Sum(Case When DatePart(mm,TS.SDate)=10 Then TS.SCount Else 0 End)	">Oct"
	,Sum(Case When DatePart(mm,TS.SDate)=11 Then TS.SCount Else 0 End)	">Nov"
	,Sum(Case When DatePart(mm,TS.SDate)=12 Then TS.SCount Else 0 End)	">Dec"
	,Sum(Case When DatePart(mm,TS.SDate)=1 Then TS.SCount Else 0 End)	">Jan"
	,Sum(Case When DatePart(mm,TS.SDate)=2 Then TS.SCount Else 0 End)	">Feb"
	,Sum(Case When DatePart(mm,TS.SDate)=3 Then TS.SCount Else 0 End)	">Mar"
	,Sum(Case When DatePart(mm,TS.SDate)=4 Then TS.SCount Else 0 End)	">Apr"
	,Sum(Case When DatePart(mm,TS.SDate)=5 Then TS.SCount Else 0 End)	">May"
	,Sum(Case When DatePart(mm,TS.SDate)=6 Then TS.SCount Else 0 End)	">Jun"
	,IsNull(Sum(Case When DatePart(mm,TS.SDate)=7 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=8 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=9 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=10 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=11 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=12 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=1 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=2 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=3 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=4 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=5 Then TS.SCount Else 0 End),0)
	+IsNull(Sum(Case When DatePart(mm,TS.SDate)=6 Then TS.SCount Else 0 End),0)	">Total"
	--,ST.*
	--,TS.*
	--,TV.*
	From TaskStatistics_T TS
	Left Join Activity_T AT on AT.ActId=TS.ActId
	Left Join Task_V TV on TV.TaskId=AT.TaskId
	Left Join Statistics_T ST on ST.SType=TS.SType
	Left Join Facility_T FT on FT.Facility=TV.Facility
	Where TaskType='Statistics' and TaskStatus<>'Deleted'
			and TS.SDate between @SDate and @EDate
	Group by TV.Facility,ST.Category,ST.Statistic,FT.EdLevel,FT.NonPublic

[blakemckenna]

So essentially it's just a counter...correct?

[blakemckenna]

Thanks for the explanation guys!

[szlamany]

Yes - just a counter in most cases.

If you are writing financial systems you would do something like SUM(AmountPaid) to add up that columns values.

Sometimes you want to count (or summarize - or better yet the term is aggregate) something and do so conditionally - that is where the CASE statement works it wonders.

[blakemckenna]

I understand what the SUM statement does. But n ow that I put the statement in perspective, it makes sense. I've just never used the SUM statement in this context before and it stumped me.

Thanks again!

[FunkyDexter]

If you didn't spend so much time pontificating and adding your fancy code tags, maybe you'd get in first.

The tortoise won in the end

(Yeah, I do tend to get a bit too verbose though)

It tends to get used as a poor mans pivot a lot:-

Select Sum(Case When Col1 = 'Val1' then 1 else o end) as [VAL1],
   Sum(Case When Col1 = 'Val2' then 1 else 0 end) as [VAL2],
   Sum(Case When Col1 = 'Val3' then 1 else 0 end) as [VAL3]
From SomeTable

[blakemckenna]

Thanks guys for your help!