group parts by row count

Question

group parts by row count

Siten0308

SSCarpal Tunnel

Points: 4404
More actions
March 25, 2021 at 1:40 am
Go to Answer
#3861846

hello I have a table similar to this:
Create table parts
(
partid int not null
serial varchar(50) not null
)

insert into parts(partid, serial)
values(1,'31511')
values(1,'961511')
values(1,'61511')
values(1,'77421')
values(1,'74522')
values(1,'329484')
values(1,'1934891')
values(1,'19348924')

so what they want is to have it grouped by for ever 5, give it a group ID, and have an output of:
1, 31511, 1
1,961511,1
1,61511,1
1,77421,1
1,74522,1
1,329484,2
1,1934891,2
etc. etc. so its incrementing the third column by 1 when its 5...
so I tried doing something like:
select *,ROW_number() over(partition by Part order by part) rownum
from parts

but that is as far as I got... and not sure...
hope someone can help, thanks in advance
Siten0308

SSCarpal Tunnel

Points: 4404
More actions
March 25, 2021 at 2:27 am
Answer
#3861857

nevermind, hope this helps, i found the solution, though just need to change the tables to match whatever tables you are doing:
https://stackoverflow.com/questions/7068235/group-by-every-n-records-in-t-sql

this example above in the link groups it by 1000, so change it to 10 or whatever.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Heh... you didn't try the code you posted. It's missing commas and the VALUES are not correct.

Be advised that the data you posted has nothing other than partid and serial to sort on and so nothing you do will guarantee the order you posted in your desired result. Since the "serial" is a string, it also won't sort in numeric order without a conversion, if that's something else you might be looking for.

So, what order do you really need the output to be in? I ask because the link you provided has solutions that seem a bit complex for this simple "grouping" task.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 2

>> hello I have a table similar to this:<<

What you posted was not a table. By definition, a table must have a key. You also don't seem to know the correct syntax for insertion statements. When you get a book on data modeling, you'll learn what an attribute property is and how to use it. For example, an integer can never be an identifier; integers have to be a quantity, magnitude or scalar value. By itself, "serial" is either an adjective or adverb, not the name of something. I am going to assume that this is what you meant.

CREATE TABLE Parts

(part_count INTEGER NOT NULL ,

serial_nbr VARCHAR(50) NOT NULL PRIMARY KEY);

INSERT INTO Parts(part_count, serial_nbr)

VALUES (1,'31511'),

(1,'961511'),

(1,'61511'),

(1,'77421'),

(1,'74522'),

(1,'329484',)

(1,'1934891'),

(1,'19348924');

>> so what they want is to have it grouped by 5, give it a group ID, and have an output of:

1, 31511, 1

1,961511,1

1,61511,1

1,77421,1

1,74522,1

1,329484,2

1,1934891,2

etc. etc. so its incrementing the third column [sic] by 1 when its 5... <<

Your mindset is wrong. Columns are not supposed to be named by an ordinal position; they have names. In fact, virtually every data element SQL has to be accessed by name. This is not completely true, but it's a mindset you should have In RDBMS, all relationships, such as groups like this, are defined by scalar values in the columns of Rows in tables. This is abstract and not physical like records in a file. Codd's 12th, principle is that all relationships must be represented in this way.

>> so I tried doing something like: <<

Actually this is a very good shot at an answer! What don't you seeing, probably because SQL has this weird syntax, is that Row_number is a function that returns scalar value. It looks like it ought to be doing something fancier.

SELECT part_group, serial_nbr,

1+ (ROW_NUMBER () OVER(PARTITION BY part_group ORDER BY serial_nbr)) /5

AS part_group_nbr

FROM Parts;

Remember your high school math? How does integer division work? It doesn't have fractions! (1/5 = 0, 2/5= 0, 3/5 = 0, 4/5 = 0, 5/5 =1, etc.) adjust the Math get the part group numbers that you want.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Your code fairs no better, Joe. Try running it and see the error.

Also, the columns in your CREATE TABLE statement don't match the columns in your SELECT statement.

And, your code produces the incorrect answer... apparently you flunked the high school math that you're talking about.

And seriously, speaking of naming conventions, even if you don't like the word "ID", why did you change the first column name to "part_count" especially after citing data modeling?

Perhaps it's time for you to start practicing what you preach, eh?

EDIT - I hit "Report" instead of "Quote" by mistake on Joe's post. I've reported it the the site manager to have it restored.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

Crud... I went to hit QUOTE and, instead, hit REPORT by mistake. My apologies, Joe. I'll contact the webmaster and let them know.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

There's an easy way to create GroupIDs by size using the ROW_NUMBER() function and a little integer math.

The example below uses a varchar column with ROW_NUMBER(), just to show that you don't have to have a pre-existing unbroken numeric sequence.

declare @members table(MemberID int identity(1,1) primary key, MemberName varchar(50))
insert into @Members
values
('Miles Standish')
,('John Smith')
,('Tom Jones')
,('Englebert Humperdink')
,('Mary Queen of Scots')
,('Mary Mary Quite Contrary')
,('Robert Bruce Banner')
,('Steve Rogers')
,('Dr. Don Blake')
,('Princess Anastasia')
,('Kvothe, son of Arliden')
,('Taborlin the Great')
,('Bob Dylan')
,('William "Smokey" Robinson')
,('Levi Stubbs')
,('David Ruffin')
,('Yusuf Islam')
,('St. Cat, the Beloved')
,('Mick Jagger')
,('Muddy Waters')
,('B.B. King')
,('Aretha Franklin')

declare @GroupSize int = 5;

with ct1 as ( select MemberID, MemberName
     ,RowID = Row_Number() over(order by MemberName) 
              from @Members)

select  MemberID, MemberName, RowID
        ,GroupID = (RowID-1)/@GroupSize + 1
from ct1

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Sergiy SSC Guru Points: 110206 More actions · Answer 6

Is it what you're after?

DECLARE @BatchId int = 10 ;
with BatchOrders (InvNo, BatchID) as (
select 1111, 1)

select count(InvNo) as RecordCount, @BatchId as BatchId
from BatchOrders
where BatchId = @BatchId

_____________
Code for TallyGenerator