Counting issue 1

[Chumley40]

I want to count by groups of 7. so, want the first seven rows in my query to be 1, the next 7 to be two, next seven 3, etc.

Any ideas on how to accomplish this?

 

[markros]

Here is the idea:

;with cte as (select *, row_number() as Row from myTable)

select SomeField, Row %8 as GroupFld from cte group by SomeField, Row%8

PluralSight Learning Library

 

[Chumley40]

I don't understand. so the first statement you wrote is basically my code with a rownumber added.

the second one, I don't get? What is row%8?

 

[markros]

% is the modulus operator in SQL Server.

So,
1/7 = 0
2/7 = 0
3/7 = 0

...
7/7 = 1
8/7 = 1

etc.

so, to organize groups of 7, we want to divide our row by 8 and check for the int.

Also, BTW, we don't need to use %. We can use / as two integers will use integer math division.

PluralSight Learning Library