If column is not present in table How to apply sum function

Sep 27 2016 7:17 AM

select 'Upender' as [Floor], <br />SUM(Agentamount) as [Tolling], <br />(Select count(distinct agentname) From crm_Leadtable where APPTDATE between '2016-09-26' and '2016-09-30' <br /> and Location in ('R6','R3') and agentamount>=1000) as <strong><span style="color: red;">ZeroKillCount</span></strong>, <br />(Select count(AgentAmount) AS [Pointer] From crm_Leadtable where Agentamount >=5000 and <br />APPTDATE between '2016-09-26' and '2016-09-30' <br />and Leadstatus = 'Done' and Location in ('R3','R6')) as [<strong style="color: red;">Pointer</strong>] ,<strong>'' as [TotalPoint] </strong><br />from <br />CRM_LeadTable where LeadStatus='Done' and <br /><div>APPTDATE between '2016-09-26' and '2016-09-30' and Location in ('R3','R6','Upender')</div><div> </div><div> </div><div> </div><div><img src="/forums/uploadfile/17154c/09272016070527AM/AM.PNG" alt="" /></div><div>in above query I want <strong>'' as [Tota</strong><strong>lPoint]  </strong>the sum of <strong><span style="color: red;">ZeroKillCount and <strong style="color: red;">Pointer</strong></span></strong></div><div> </div><div> I wrote :</div><div> </div><div> </div><div>select 'Upender' as [Floor], <br />SUM(Agentamount) as [Tolling], <br />(Select count(distinct agentname) From crm_Leadtable where APPTDATE between '2016-09-26' and '2016-09-30' <br /> and Location in ('R6','R3') and agentamount>=1000) as ZeroKillCount, <br />(Select count(AgentAmount) AS [Pointer] From crm_Leadtable where Agentamount >=5000 and <br />APPTDATE between '2016-09-26' and '2016-09-30' <br />and Leadstatus = 'Done' and Location in ('R3','R6')) as [Pointer] ,<strong><span style="color: red;">SUM(CONVERT(INT, ZeroKillCount)+CONVERT(INT, Pointer)) as [TotalPoint] </span></strong><br />from <br />CRM_LeadTable where LeadStatus='Done' and <br />APPTDATE between '2016-09-26' and '2016-09-30' and Location in ('R3','R6','Upender') </div><div> </div><div> </div><div>But it giving error column not found <strong><span style="color: red;">ZeroKillCount and <strong><span style="color: red;">Pointer</span></strong></span></strong></div><div> </div><div><strong><span style="color: #0000ff;">I want Result as:</span></strong></div><div> <img src="/forums/uploadfile/17154c/09272016070527AM/AM1.PNG" alt="" /></div><div> </div><div> </div><div> </div><div> </div><div> </div><div> </div><div> </div><div> </div>

Answers (2)

Distribution of temp db in multiple files

When Generating scripts , do we need to run all the code?