How to show zero value

Question

Hey I have an issue that I have to show zero amount if there is no record in the table column. select DATEPART(mmApptDate)as Month SUM(coalesce(AgentAmount0)) as [Tolling] from Test.dbo.LeadTable where DATEPART(mmApptDate)='1' and DATEPART(yyApptDate)='2017' GROUP BY DATEPART(mmApptDate) There is no data in jan 2017 but i want to show 0 value. Please help me

Nitin Sontakke · Answer

You can try something like this: select m.[ Month ] t.Tolling from ( select 1 as [ Month ] union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 ) m left outer join ( select DATEPART(mmApptDate) as [ Month ] SUM ( coalesce (AgentAmount0)) as [Tolling] from Test.dbo.LeadTable where DATEPART(mmApptDate)= '1' and DATEPART(yyApptDate)= '2017' GROUP BY DATEPART(mmApptDate) ) t on m.[ Month ] = t.[ Month ] where m.[ Month ] = 1

Satyaprakash Samantaray · Answer

use isnull function in sql

pritaeas · Answer

Create a dummy table with a record for each month (1..12) so you can use it in a join.

Ramesh Palanivel · Answer

Hi Vandana Try this URL It will help you to solve your issue they explained well. http://stackoverflow.com/questions/17839212/return-zero-if-no-record-is-found