January 2, 2019 at 1:27 pm
I want to count the number of records in a batch of records with multiple batches.
example:
table1
ID col2 col3
1 0 0
2 0 0
3 -1 -1
4 0 -1
5 -1 0
6 0 0
7 -1 0
8 -1 0
9 0 -1
10 0 0
11 -1 0
12 0 0
say i want a count for recs between 1 and 4, 5 and 8, 9 and 12
I want to return the count that has col2 or col3 = -1
so my counts would be
2,3,2
or
2
3
2
whether the results display in rows or in 1 row doesn't matter to me.
January 2, 2019 at 1:47 pm
This doesn't make much sense. Are the ranges hardcoded or variable? how are they determined? You can simply do a sum and a case when to handle it, but it's not clear
drop table if exists #t
create table #t (
ID int,
col2 int,
col3 int)
insert into #t values
(1 ,0, 0)
,(2 ,0, 0)
,(3 ,-1, -1)
,(4 ,0 ,-1)
,(5 ,-1, 0)
,(6 ,0 ,0)
,(7 ,-1, 0)
,(8 ,-1, 0)
,(9 ,0 ,-1)
,(10, 0 ,0)
,(11, -1, 0)
,(12, 0 ,0)
select sum(case when ID between 1 and 4 and (Col2 = -1 or Col3 = -1) then 1 else 0 end) ID1_4,
sum(case when ID between 5 and 8 and (Col2 = -1 or Col3 = -1) then 1 else 0 end) ID5_8,
sum(case when ID between 9 and 12 and (Col2 = -1 or Col3 = -1) then 1 else 0 end) ID9_12
from #t
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply