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
 total is wrong, why ?

Author  Topic 

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-26 : 01:52:11
Dear Sir,

I write this query but the total is wrong on 1 to 3 rec, only last record total 4 is right, please help to edit this query ?

case when salesman_code is null then 0 else 1 end +
case when salesman_code2 is null then 0 else 1 end +
case when salesman_code3 is null then 0 else 1 end +
case when salesman_code4 is null then 0 else 1 end as total


---------------------------------------------------
Data Sample
salesman_code,salesman_code2,salesman_code3,salesman_code4, total
staff-01 2
blank space blank space blank space blank space 2
staff-01 staff-03 3
staff-02 staff-01 staff-6 staff-04 4
-----------------------------------------------------

pascal_jimi
Posting Yak Master

167 Posts

Posted - 2015-01-26 : 03:42:37
hi

where hier error sum case when ...?

your script sum case when .. uses succefuly
declare @test table
(id int not null identity(1,1),
salesman_code4 nchar(20) null,
salesman_code5 nchar(20) null,
salesman_code6 nchar(20) null)

insert into @test
(salesman_code4,salesman_code5,salesman_code6)
select null,'1','2'
union
select '1','2',null

select*from @test


select 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 ) from @test



(????? ??????????: 2)
id salesman_code4 salesman_code5 salesman_code6
----------- -------------------- -------------------- --------------------
1 NULL 1 2
2 1 2 NULL

(????? ??????????: 2)


-----------
4

(????? ??????????: 1)


http://sql-az.tr.gg/
Go to Top of Page

usafelix
Posting Yak Master

165 Posts

Posted - 2015-01-26 : 05:31:28
the problem is fixed.
Go to Top of Page
   

- Advertisement -