How to autoincrement a column by group using group by (sql server 2000)

Question

How to autoincrement a column by group using group by (sql server 2000)

leonardohickstein

Old Hand

Points: 304
More actions
August 19, 2008 at 1:29 pm

#72906

[font="Courier New"]Is there a way to autoincrement a column by group using group by function under sql server 2000?
Something like:
SELECT
storeid,
storename,
requirement,
SUM(last_total),
SUM(actual_total),
SUM(daily_average),
???? AS counter
FROM
tableX
GROUP BY
storeid,
storename,
requirement
My actual data:
storeid | storename | requirement | last_total | actual_total | daily_average
--------------------------------------------------------------------------------
4 | store a | req 1 | 10 | 12 | 1
4 | store a | req 2 | 8 | 33 | 1
4 | store a | req 3 | 7 | 3 | 0
8 | store b | req 1 | 10 | 12 | 1
8 | store b | req 2 | 8 | 33 | 1
8 | store b | req 3 | 7 | 3 | 0
And what I want:
storeid | storename | requirement | last_total | actual_total | daily_average | counter
---------------------------------------------------------------------------------------
4 | store a | req 1 | 10 | 12 | 1 | 1
4 | store a | req 2 | 8 | 33 | 1 | 2
4 | store a | req 3 | 7 | 3 | 0 | 3
8 | store b | req 1 | 10 | 12 | 1 | 1
8 | store b | req 2 | 8 | 33 | 1 | 2
8 | store b | req 3 | 7 | 3 | 0 | 3
Thanks guys!
Leonardo Hickstein[/font]

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

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

ZA_Crafty Ten Centuries Points: 1228 More actions · Answer 1

This can get very hairy, very quickly...

Perhaps create 2 temp tables. then a cursor for the store, then populate a temp table with a identity column. then insert that table into the temp table with no identity.

just check your indexes as this can get very slow if not optimised.

leonardohickstein (8/19/2008)
[font="Courier New"]Is there a way to autoincrement a column by group using group by function under sql server 2000?
Something like:
SELECT
storeid,
storename,
requirement,
SUM(last_total),
SUM(actual_total),
SUM(daily_average),
???? AS counter
FROM
tableX
GROUP BY
storeid,
storename,
requirement
My actual data:
storeid | storename | requirement | last_total | actual_total | daily_average
--------------------------------------------------------------------------------
4 | store a | req 1 | 10 | 12 | 1
4 | store a | req 2 | 8 | 33 | 1
4 | store a | req 3 | 7 | 3 | 0
8 | store b | req 1 | 10 | 12 | 1
8 | store b | req 2 | 8 | 33 | 1
8 | store b | req 3 | 7 | 3 | 0
And what I want:
storeid | storename | requirement | last_total | actual_total | daily_average | counter
---------------------------------------------------------------------------------------
4 | store a | req 1 | 10 | 12 | 1 | 1
4 | store a | req 2 | 8 | 33 | 1 | 2
4 | store a | req 3 | 7 | 3 | 0 | 3
8 | store b | req 1 | 10 | 12 | 1 | 1
8 | store b | req 2 | 8 | 33 | 1 | 2
8 | store b | req 3 | 7 | 3 | 0 | 3
Thanks guys!
Leonardo Hickstein[/font]

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Visit the following URL

http://www.sqlservercentral.com/Forums/Topic551833-8-1.aspx

karthik

Amit Khanna-455738 SSC Eights! Points: 865 More actions · Answer 3

There is better way to achieve the same. Please find the following below script:

Something like:

SELECT

storeid,

storename,

requirement,

SUM(last_total),

SUM(actual_total),

SUM(daily_average),

ROW_NUMBER () OVER (PARTITION BY storeid ORDER BY storeid) AS counter -- Code Added

FROM

tableX

GROUP BY

storeid,

storename,

requirement

This will not have the overhead of creating temporary tables.

Thanks,

Amit Khanna

karthik M One Orange Chip Points: 29139 More actions · Answer 4

ROW_NUMBER () OVER (PARTITION BY storeid ORDER BY storeid) AS counter -- Code Added

I think sql 2000 doesn't have this feature. OP's question below.

Is there a way to autoincrement a column by group using group by function under sql server 2000?

karthik

leonardohickstein Old Hand Points: 304 More actions · Answer 5

In fact I read about this new sqlserver 2005 function that would fit perfectly but my database still under sqlserver 2000 🙁

ROW_NUMBER () OVER (PARTITION BY storeid ORDER BY storeid) AS counter -- Code Added

I'm using cursors to work around this issue but I thought it would have a better approach to solve this.

Leonardo Hickstein

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 6

Read the following article. It will solve your problem. http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

Or, if you post some DDL and sample data we can give you better help 🙂