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
 Group by command

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_counter

you don't need sum() here...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

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.
Go to Top of Page

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 end

Regards
Viggneshwar A
Go to Top of Page
   

- Advertisement -