How to autoincrement a column by group using group by (sql server 2000)
-
[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]
-
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]
-
-
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
-
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
-
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
-
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply