Invalid column name "total_counter"

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

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-23 : 04:11:08

error query.
invalid column name bonus

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.

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources