Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Invalid column name "total_counter"

Author  Topic 

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-23 : 02:32:01
Anyone help ?

Error Message "invalid column name on total_counter ". How to edit this query and make it work ?

SELECT salesman_code,user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,
salesman_code4,salesman_code5,salesman_code6,

Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) as total_counter,

case when trx_acc_amt>=1000 then trx_acc_amt-1000/total_counter
else 0 end as bonus,

case when trx_acc_amt<1000 then trx_acc_amt/total_counter
else 0 end as nobonus


from trx_hdr
inner join user_hdr on user_hdr.user_id = trx_hdr.salesman_code
inner join user_dat on user_dat.user_id = trx_hdr.salesman_code
where user_group = 'CON' or user_pos = 'THRAP-USER' or salesman_code4<>'' or salesman_code5<>'' or salesman_code6<>''
group by salesman_code, user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6 , total_counter, bonus, nobonus

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-23 : 02:55:11
SELECT salesman_code,user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,
salesman_code4,salesman_code5,salesman_code6,

Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) as total_counter,

case when trx_acc_amt>=1000 then trx_acc_amt-1000/ ( Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) )
else 0 end as bonus,

case when trx_acc_amt<1000 then trx_acc_amt/( Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) )
else 0 end as nobonus


from trx_hdr
inner join user_hdr on user_hdr.user_id = trx_hdr.salesman_code
inner join user_dat on user_dat.user_id = trx_hdr.salesman_code
where user_group = 'CON' or user_pos = 'THRAP-USER' or salesman_code4<>'' or salesman_code5<>'' or salesman_code6<>''
group by salesman_code, user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6 , bonus, nobonus

Regards
Viggneshwar A
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-23 : 04:11:08
error query.
invalid column name bonus
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-23 : 04:13:26
error query.

invalid oolumn name bonus and divide by zero error encountered.
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-23 : 04:23:28
Hi Viggneshwar A,

My desire the calcuate is this condition

if trx_acc_amt<1000
then trx_acc_amt/total_counter
as below_1000_bonus
else
trx_acc_amt>=1000
then trx_acc_amt*0.1
as over_1000_bonus
end

Go to Top of Page

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-23 : 05:41:55
SELECT salesman_code,user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,
salesman_code4,salesman_code5,salesman_code6,

Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) as total_counter,

sum(case when trx_acc_amt>=1000 then trx_acc_amt-1000/ nullif(( Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) ),0 )
else 0 end) as bonus,

sum(case when trx_acc_amt<1000 then trx_acc_amt/( nullif(Sum(case when salesman_code4 is null then 0 else 1 end +
case when salesman_code5 is null then 0 else 1 end +
case when salesman_code6 is null then 0 else 1 end) ),0)
else 0 end) as nobonus


from trx_hdr
inner join user_hdr on user_hdr.user_id = trx_hdr.salesman_code
inner join user_dat on user_dat.user_id = trx_hdr.salesman_code
where user_group = 'CON' or user_pos = 'THRAP-USER' or salesman_code4<>'' or salesman_code5<>'' or salesman_code6<>''
group by salesman_code, user_group,user_name,trx_acc_amt,trx_date,trx_no,sh_code,salesman_code4,salesman_code5,salesman_code6


Regards
Viggneshwar A
Go to Top of Page
   

- Advertisement -