Vandana

Vandana

  • 1.1k
  • 566
  • 72.3k

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&gt;=1000) as <strong><span style="color: red;">ZeroKillCount</span></strong>, <br />(Select count(AgentAmount) AS [Pointer] From crm_Leadtable where Agentamount &gt;=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>&nbsp;</div><div>&nbsp;</div><div>&nbsp;</div><div><img src="/forums/uploadfile/17154c/09272016070527AM/AM.PNG" alt="" /></div><div>in above query I want&nbsp;<strong>'' as [Tota</strong><strong>lPoint]&nbsp; </strong>the sum of <strong><span style="color: red;">ZeroKillCount and <strong style="color: red;">Pointer</strong></span></strong></div><div>&nbsp;</div><div>&nbsp;I wrote :</div><div>&nbsp;</div><div>&nbsp;</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&gt;=1000) as ZeroKillCount, <br />(Select count(AgentAmount) AS [Pointer] From crm_Leadtable where Agentamount &gt;=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')&nbsp;</div><div>&nbsp;</div><div>&nbsp;</div><div>But it giving error column not found&nbsp;<strong><span style="color: red;">ZeroKillCount and <strong><span style="color: red;">Pointer</span></strong></span></strong></div><div>&nbsp;</div><div><strong><span style="color: #0000ff;">I want Result as:</span></strong></div><div>&nbsp;<img src="/forums/uploadfile/17154c/09272016070527AM/AM1.PNG" alt="" /></div><div>&nbsp;</div><div>&nbsp;</div><div>&nbsp;</div><div>&nbsp;</div><div>&nbsp;</div><div>&nbsp;</div><div>&nbsp;</div><div>&nbsp;</div>

Answers (2)