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

Question

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>

Answer 1

Correct sSql string is follows..
sSQL = "Insert into Transaction_Line (TransL_id,Trans_id, TL_dt, Pro_id, Quantity)" & _
"values('" & TransLid & "', '" & transID & "', '" & TLdte & "', '" & proID & "', '" & quan & "')"

Change transID Position in values it will be in 2nd position.
 

Answer 2

hi

i think you have taken date field value int not datetime or varchar . That's why you are getting this error.

please change it to datetime.

Don't forget to Mark Do you like this Answer  that solved your problem!

Answer 3

TransL_id: Varchar(25)
Trans_id: int
Pro_id: Varchar(15)
Quantity: numeric(4, 0)
Total_price: numeric(10, 2)
TL_dt: varchar(50)
Share_h_id: varchar(10)

For the TL_dt  tried a dateandtime format but its not working at all it always retrieves an error... therefore i tried the varchar instead

Answer 4

Hi,
The error is  coming due to this sentence,
sSQL = "Insert into Transaction_Line (TransL_id,Trans_id, TL_dt, Pro_id, Quantity)" & _
"values('" & TransLid & "', '" & TLdte & "', '" & proID & "', '" & quan & "', '" & transID & "')"
 
In that u r trying to insert..
TransL_id (int)- > TransLid (int),
Trans_id (int) - > TLdte (date),
TL_dt (date) -> proID (int),
Pro_id - > quan ,
Quantity - > transID

Answer 5

Can you also post Transaction_Line table structure (Fields with DataType) ?