A sql statement, master challenges about it

Category: MS-SQL Server -> Applications Author: hedaye111 Date: 2002-05-21 18:16:42
 
hedaye111
2002-05-21 18:16:42

select ____ as 
from table1


table1:
Type Value
A 2
A 4
B 0
B 5
C 0

results:
nonzero number
2

type of explanation:
A non-zero record , B has a non-zero record, C is not zero records, so the result is 2.

Requirements:
can not be used where conditions, but you can use group by and order by, spaces can count, sum, etc., can not be too slow query speed.

empty Gerry how to write, ask for advice! !
shelleyan2008
2002-05-21 18:20:58

declare @table1 table(c1 varchar(1),c2 int)
insert into @table1
select 'A', 2 union all
select 'A', 4 union all
select 'A', 4 union all
select 'B', 0 union all
select 'B', 5 union all
select 'C', 0



select count(distinct case when c2 > 0 then c1 else null end ) as 
from @table1
kelinsi
2002-05-21 18:28:27
SUM (CASE WHEN value! = 0 then 1 else 0 end)
xiaokja
2002-05-21 18:39:04

declare @table1 table(c1 varchar(1),c2 int)
insert into @table1
select 'A', 2 union all
select 'A', 4 union all
select 'B', 0 union all
select 'B', 5 union all
select 'C', 0

select sum(cnt)   from
(
select case when max(c2)>0 then 1 else 0 end cnt
from @table1
group by c1
)t

/*

-----------
2
*/

k5894602
2002-05-21 18:50:19
SUM (CASE WHEN EXISTS (SELECT 1 FROM TABLE1 WHERE value <> 0) THEN 1 ELSE 0 END)
mengzhixi
2002-05-21 19:04:09

declare @table1 table([ ] varchar(1),[ ] int)
insert @table1
select 'A',2 union all
select 'A',4 union all
select 'B',0 union all
select 'B',5 union all
select 'C',0

select top 1 sum(1) as 
from @table1 t group by [ ] having(sum([ ])<>0)
/*

-----------
2
*/
honeypaop
2002-05-21 19:09:10
select top 1 sum (1) as Type nonzero number from @ table1 group by [type] having (sum ([value]) <> 0)

should be considered to meet the meaning of the questions. ..
j77k88
2002-05-21 19:15:47


this positive solution