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.
| Author |
Topic |
|
usafelix
Posting Yak Master
165 Posts |
 Posted - 2015-01-23 : 00:28:45
|
| This query is error if missing the group command, how I can edit this query make it work but not using the group command . SELECT trx_hdr.salesman_code,user_hdr.user_pos,user_hdr.user_name,trx_hdr.trx_acc_amt,trx_hdr.trx_date,trx_hdr.trx_no,trx_hdr.sh_code,trx_hdr.salesman_code4,trx_hdr.salesman_code5,trx_hdr.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 from trx_hdr inner join user_hdr on user_hdr.user_id = trx_hdr.salesman_code |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2015-01-23 : 02:33:58
|
without grouping you can get only the sum of code4,5 and 6 of the actual row.Is that what you need? then just do isnull(code4,0) + isnull(code5,0) + isnull(code6,0) as total_counteryou don't need sum() here...
Too old to Rock'n'Roll too young to die. |
 |
|
|
usafelix
Posting Yak Master
165 Posts |
Posted - 2015-01-23 : 04:34:20
|
| i try it isnull(code4,0) + isnull(code5,0) + isnull(code6,0) as total_counter, but display the total_counter is not numeric and include of text. That is not my expect result. for example this code4 is 1 , code5 is "" , code6 is 1 . code4+code5+code6 = the result total_counter is 2. |
|
|
|
viggneshwar
Yak Posting Veteran
86 Posts |
Posted - 2015-01-23 : 05:04:20
|
| case when isnumeric(code4)=1 then code4 else 0 end + case when isnumeric(code5)=1 then code5 else 0 end + case when isnumeric(code6)=1 then code6 else 0 endRegardsViggneshwar A |
|
|
|
|
|
|
|
|
Group by command