SUM(Agentamount) as [Tolling],
(Select count(distinct agentname) From crm_Leadtable where APPTDATE between '2016-09-26' and '2016-09-30'
and Location in ('R6','R3') and agentamount>=1000) as ZeroKillCount,
(Select count(AgentAmount) AS [Pointer] From crm_Leadtable where Agentamount >=5000 and
APPTDATE between '2016-09-26' and '2016-09-30'
and Leadstatus = 'Done' and Location in ('R3','R6')) as [Pointer] ,'' as [TotalPoint]
from
CRM_LeadTable where LeadStatus='Done' and
APPTDATE between '2016-09-26' and '2016-09-30' and Location in ('R3','R6','Upender')

in above query I want '' as [TotalPoint] the sum of ZeroKillCount and Pointer
I wrote :
select 'Upender' as [Floor],
SUM(Agentamount) as [Tolling],
(Select count(distinct agentname) From crm_Leadtable where APPTDATE between '2016-09-26' and '2016-09-30'
and Location in ('R6','R3') and agentamount>=1000) as ZeroKillCount,
(Select count(AgentAmount) AS [Pointer] From crm_Leadtable where Agentamount >=5000 and
APPTDATE between '2016-09-26' and '2016-09-30'
and Leadstatus = 'Done' and Location in ('R3','R6')) as [Pointer] ,SUM(CONVERT(INT, ZeroKillCount)+CONVERT(INT, Pointer)) as [TotalPoint]
from
CRM_LeadTable where LeadStatus='Done' and
APPTDATE between '2016-09-26' and '2016-09-30' and Location in ('R3','R6','Upender')
SUM(Agentamount) as [Tolling],
(Select count(distinct agentname) From crm_Leadtable where APPTDATE between '2016-09-26' and '2016-09-30'
and Location in ('R6','R3') and agentamount>=1000) as ZeroKillCount,
(Select count(AgentAmount) AS [Pointer] From crm_Leadtable where Agentamount >=5000 and
APPTDATE between '2016-09-26' and '2016-09-30'
and Leadstatus = 'Done' and Location in ('R3','R6')) as [Pointer] ,SUM(CONVERT(INT, ZeroKillCount)+CONVERT(INT, Pointer)) as [TotalPoint]
from
CRM_LeadTable where LeadStatus='Done' and
APPTDATE between '2016-09-26' and '2016-09-30' and Location in ('R3','R6','Upender')
But it giving error column not found ZeroKillCount and Pointer
I want Result as:


Midhun TpPosted Sep 27, 2016, 7:32 AM
VandanaPosted Sep 27, 2016, 8:54 AM