Min() Max() and Avg() return 0 instead of NULL? 1

[HestonJames]

Good morning all,

I've got the following query:

Select  c.yyyy,
		c.mm,
		h.hhname,
		Max(f.MessageCount) As HighestSendsToOneDevice,
		Min(f.MessageCount) As LowestSendsToOneDevice,
		Avg(cast(f.MessageCount as float)) As AverageSendsToOneDevice
From    Calendar As c
Cross Join Hours As h
Left Outer Join    (    
				Select      MessageLog.Device_ID,
							Count(Distinct MessageLog.Media_ID) AS MessageCount,
							DATEADD(hh,DATEDIFF(hh, 0, MessageLog.LogDateTime), 0) As LogDate
				From        MessageLog
				Inner Join    LogClass
				On            LogClass.LogClass_ID = MessageLog.LogClass_ID
				Where        LogClass.Name = 'org.openobex.TransferComplete'
				And            MessageLog.ThinkTank_ID = 67
				And            MessageLog.LogDateTime >= '20081105'
				And            MessageLog.LogDateTime < '20081106'
				Group By    MessageLog.Device_ID,
							DATEADD(hh,DATEDIFF(hh, 0, MessageLog.LogDateTime), 0)
				) As f
				On f.LogDate = dateadd(hh, h.hh, c.caldate)
Where		c.caldate >= '20081105'
And			c.caldate < '20081106'
Group By    c.yyyy,
			c.mm,
			h.hhname

This works exactly I would expect it to, however, when the subquery returns 0 rows then the columns 'HighestSendsToOneDevice', 'LowestSendsToOneDevice' and 'AverageSendsToOneDevice' return as NULL whereas I would really rather have them return as 0 as this is easier for me to handle and chart in my front end application.

What is the simplest way to change this so that the columns return 0 instead of NULL is no rows are found in the subquery?

Many thanks guys, I appreciate any advice.

Heston

 

[EKOnerhime]

Untested but should work:

Select  c.yyyy,
        c.mm,
        h.hhname,
        ISNULL(Max(f.MessageCount),0) As HighestSendsToOneDevice,
        ISNULL(Min(f.MessageCount),0) As LowestSendsToOneDevice,
        ISNULL(Avg(cast(f.MessageCount as float)),0) As AverageSendsToOneDevice
From    Calendar As c
Cross Join Hours As h
Left Outer Join    (    
                Select      MessageLog.Device_ID,
                            Count(Distinct MessageLog.Media_ID) AS MessageCount,
                            DATEADD(hh,DATEDIFF(hh, 0, MessageLog.LogDateTime), 0) As LogDate
                From        MessageLog
                Inner Join    LogClass
                On            LogClass.LogClass_ID = MessageLog.LogClass_ID
                Where        LogClass.Name = 'org.openobex.TransferComplete'
                And            MessageLog.ThinkTank_ID = 67
                And            MessageLog.LogDateTime >= '20081105'
                And            MessageLog.LogDateTime < '20081106'
                Group By    MessageLog.Device_ID,
                            DATEADD(hh,DATEDIFF(hh, 0, MessageLog.LogDateTime), 0)
                ) As f
                On f.LogDate = dateadd(hh, h.hh, c.caldate)
Where        c.caldate >= '20081105'
And            c.caldate < '20081106'
Group By    c.yyyy,
            c.mm,
            h.hhname

 

[HestonJames]

EKOnerhime,

That worked an absolute charm!! Thanks.

Heston