# How do I row count into groups?

I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them in set 1. Next three are 0,0,1 and I want them in set 2.

Order is important as I'm matching against a pattern table to get an overall count of the binary patterns.

I can see that Row would help but I don't know how to make row count by 3'ss.

Thank you.

• look at this question on StackOverflow: stackoverflow.com/q/7144100/136639, I hope it helps Aug 23, 2011 at 1:45
• How are you identifying "first three rows"? what version of SQL Server? Aug 23, 2011 at 2:32
• If you have 3 bit columns, there are 8 permutations unless you are saying that only one column or the 3 can be set. Or do you have one bit column where you want to group adjacent rows: we need to know what makes "adjacent". And are there any other bit patterns eg 110?
– gbn
Aug 23, 2011 at 4:38

Rows in a table don't have order. You will need an additional column to enforce the order, if order is important for you.

If you have a column that preserves order, here is how you could do it in Oracle. You will have to translate to SQL Server.

``````SELECT f1a, f1b, f1c, count(*) FROM (
SELECT LAG(f1,2) OVER (ORDER BY f2) f1a, LAG(f1, 1) OVER (ORDER BY f2) f1b, f1 f1c, f2
from t1
)
WHERE MOD(f2,3) = 0
GROUP BY f1a, f1b, f1c;
``````

Test Data:

``````--DROP TABLE t1;
CREATE TABLE t1 AS
SELECT 0 f1, 1 f2 from dual
UNION ALL
SELECT 1, 2 from dual
UNION ALL
SELECT 0, 3 from dual
UNION ALL
SELECT 1, 4 from dual
UNION ALL
SELECT 0, 5 from dual
UNION ALL
SELECT 0, 6 from dual
UNION ALL
SELECT 0, 7 from dual
UNION ALL
SELECT 0, 8 from dual
UNION ALL
SELECT 1, 9 from dual
UNION ALL
SELECT 1, 10 from dual
UNION ALL
SELECT 0, 11 from dual
UNION ALL
SELECT 0, 12 from dual
;
``````

Based on the following assumptions:

1. You already know what order you want your data in and how to do it - in particular, your ordering does not allow ties
2. You have 1 bit field in each row
3. You want to concatenate the bit fields in each 'set' of 3 'adjacent' rows
4. Your version of SQL Server supports windowing functions

You can use the windowing function `row_number()` and divide by three to group the rows into 3s, perhaps like this:

testbed:

``````create table t(id int identity, flag bit default round(rand(),0))
while (select count(*) from t)<21
begin
insert into t default values
end
``````

query:

``````select grp, str(sum(case rnk%3 when 0 then cast(flag as integer) end))+
str(sum(case rnk%3 when 1 then cast(flag as integer) end))+
str(sum(case rnk%3 when 2 then cast(flag as integer) end)) pattern
from( select id, flag, row_number() over (order by id) - 1 as rnk,
floor((row_number() over (order by id) - 1)/3) as grp
from t ) as z
group by grp
``````

result:

``````grp          pattern
0            0         1         0
1            0         0         0
2            1         1         1
3            0         1         0
4            0         0         0
5            1         0         1
6            1         0         0
``````

You'll need to substitute your ordering for my `order by id`s - and probably do something with the pattern to make it match with your pattern table :)