Return 0 When Result Is Empty

I have this statement: SELECT COUNT(AccedentId) AS NumberOfAccedentInYear FROM Accident GROUP BY DriverId, YEAR(AccedentDate) HAVING

Solution 1:

Maybe you wanted this (I'm keeping obvious syntax and spelling errors that I can't really correct without more information):

SELECT NumberOfAccedentInYear = ISNULL
(
  (SELECTCOUNT(AccedentId)
   FROM         Accident
   GROUPBY DriverId, YEAR(AccedentDate)
   HAVING     (DriverId =@DriverId)<3))
   , 0
);

For anyone curious about the COALESCE vs ISNULL discussion, and why I changed my answer to use ISNULL, @kanav rightly pointed out that COALESCE is more expensive. COALESCE evaluates the subquery twice, as I explained here: https://stackoverflow.com/a/10669660/61305