Count per Mutatie_Id - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count per Mutatie_Id

Author

Topic

gnaus
Starting Member

41 Posts

Posted - 2011-10-06 : 05:17:54

Dear reader,

See the table below:
There are 2 colums.
You see, The Mutatie_id has more than 1 record per Mutatie_id.

Id Mutatie_id
18 3903
19 3903
20 3903
21 3904
22 3904
23 3904
24 3905
25 3905
26 3906
27 3906
28 3906
29 3907
30 3908
31 3909
32 3909

I want to add an extra column (that’s not the problem ;-) ) and in that column I want to place numbers(count) per Mutatie_Id
So I want a result like this:

Id Mutatie_id Nw_column
18 3903 1
19 3903 2
20 3903 3
21 3904 1
22 3904 2
23 3904 3
24 3905 1
25 3905 2
26 3906 1
27 3906 2
28 3906 3
29 3907 1
30 3908 1
31 3909 1
32 3909 2

So I want a count per Mutatie_Id.
So Mutatie_Id 3903 has three records, so the counting is: first record 1, second record 2, third record 3.
Mutatie_Id 3909 for example has tow records, so the first is 1 and the second is 2

Maybe I want to give the numbers in a sort order: ascend on MutatieDatum

Thank you very much!

Greetings, GN

GN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-06 : 05:20:31

row_number() is your friend

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-06 : 05:22:22

select Id, Mutatie_id, row_number() over (partition by Mutatie_id order by Id ASC) as Nw_column from table

No, you're never too old to Yak'n'Roll if you're too young to die.

gnaus
Starting Member

41 Posts

Posted - 2011-10-06 : 06:34:16

dear webfred,

thank you!!! it works perfectly!!
only one question: he shows the result but hij doesn't place the result in the column.
I can do that myself by some actions but maybe there's a quicker manner to do this (in one command?)

greetings, GN

GN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-06 : 06:42:21

update t
set Nw_column = dt.Nw_column
from table as t
join
(select Id, Mutatie_id, row_number() over (partition by Mutatie_id order by Id ASC) as Nw_column from table)dt
on dt.Id = t.Id

No, you're never too old to Yak'n'Roll if you're too young to die.

gnaus
Starting Member

41 Posts

Posted - 2011-10-06 : 07:00:45

t= table and dt= new table?
because, I don't have a 'dt'..
a new table is not created yet

maybe I misunderstand..

GN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-06 : 07:08:52

trust me it will work.
dt is the alias for the dereived table:
(select Id, Mutatie_id, row_number() over (partition by Mutatie_id order by Id ASC) as Nw_column from table)dt

You only have to change "table" by the name of your table.

No, you're never too old to Yak'n'Roll if you're too young to die.

gnaus
Starting Member

41 Posts

Posted - 2011-10-06 : 07:35:29

yes you're right!
I got the whole formula now.
however he gives an error on the server-name so I go try some things. If I can't get out, I'll post here again!

thanks a lot for so far!!!!

GN

gnaus
Starting Member

41 Posts

Posted - 2011-10-06 : 08:08:41

So strange..

Here below, ‘he’ recognizes the server: (everything works)

insert
[ABFTDB01].[ABR_NAV50SP1_TST_GOOF].[ABRONA\gnaus].[dbo.Oefenomgeving$autRegForm_Verzekeringen]

select * from
[ABFPDB01].[Intellact].dbo.autRegForm_Verzekeringen

In the next, ‘he’ also recognizes the server (everything Works):

select ID,Mutatie_ID,MutatieDatum, row_number() over (partition by Mutatie_ID order by MutatieDatum ASC)
as Nummer
from
[ABFTDB01].[ABR_NAV50SP1_TST_GOOF].[ABRONA\gnaus].[dbo.Oefenomgeving$autRegForm_Verzekeringen]

But in the last, he doesn’t:

update [ABFTDB01].[ABR_NAV50SP1_TST_GOOF].[ABRONA\gnaus].[dbo.Oefenomgeving$autRegForm_Verzekeringen]
set Nummer = dt.Nummer
from [ABFTDB01].[ABR_NAV50SP1_TST_GOOF].[ABRONA\gnaus].[dbo.Oefenomgeving$autRegForm_Verzekeringen]
join

(select ID,Mutatie_ID,MutatieDatum, row_number() over (partition by Mutatie_ID order by MutatieDatum ASC)
as Nummer
from
[ABFTDB01].[ABR_NAV50SP1_TST_GOOF].[ABRONA\gnaus].[dbo.Oefenomgeving$autRegForm_Verzekeringen])

dt on dt.ID=[ABFTDB01].[ABR_NAV50SP1_TST_GOOF].[ABRONA\gnaus].[dbo.Oefenomgeving$autRegForm_Verzekeringen].ID

he gives error:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'ABFTDB01' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

GN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-06 : 08:34:02

Not sure about the cause but try using an alias:

update t
set Nummer = dt.Nummer
from [ABFTDB01].[ABR_NAV50SP1_TST_GOOF].[ABRONA\gnaus].[dbo.Oefenomgeving$autRegForm_Verzekeringen] as t
join
(select ID,Mutatie_ID,MutatieDatum, row_number() over (partition by Mutatie_ID order by MutatieDatum ASC)
as Nummer
from
[ABFTDB01].[ABR_NAV50SP1_TST_GOOF].[ABRONA\gnaus].[dbo.Oefenomgeving$autRegForm_Verzekeringen])
dt on dt.ID=t.ID

No, you're never too old to Yak'n'Roll if you're too young to die.

gnaus
Starting Member

41 Posts

Posted - 2011-10-06 : 08:39:24

thanks,
bu,
it's a pity. still the same error...

GN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-06 : 09:12:46

can you do it directly on the destination server without the need for a linked server?

No, you're never too old to Yak'n'Roll if you're too young to die.

gnaus
Starting Member

41 Posts

Posted - 2011-10-06 : 09:33:31

yes I'll try that

GN

gnaus
Starting Member

41 Posts

Posted - 2011-10-07 : 03:36:22

it still doesn't word :(

update Oefenomgeving$autRegForm_Verzekeringen
set Nummer = dt.Nummer
from Oefenomgeving$autRegForm_Verzekeringen
join

(select ID,Mutatie_ID,MutatieDatum, row_number() over (partition by Mutatie_ID order by MutatieDatum ASC)
as Nummer
from
Oefenomgeving$autRegForm_Verzekeringen)

dt on dt.ID=Oefenomgeving$autRegForm_Verzekeringen.ID

GN

gnaus
Starting Member

41 Posts

Posted - 2011-10-07 : 03:40:25

sorry I mean: "doesn't worK"

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Oefenomgeving$autRegForm_Verzekeringen'.
and If I do this:

update [ABRONA\gnaus].dbo.Oefenomgeving$autRegForm_Verzekeringen
set Nummer = dt.Nummer
from [ABRONA\gnaus].dbo.Oefenomgeving$autRegForm_Verzekeringen
join

(select ID,Mutatie_ID,MutatieDatum, row_number() over (partition by Mutatie_ID order by MutatieDatum ASC)
as Nummer
from
[ABRONA\gnaus].dbo.Oefenomgeving$autRegForm_Verzekeringen)

dt on dt.ID=[ABRONA\gnaus].dbo.Oefenomgeving$autRegForm_Verzekeringen.ID

then error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'ABRONA\gnaus.dbo.Oefenomgeving$autRegForm_Verzekeringen'.

GN

gnaus
Starting Member

41 Posts

Posted - 2011-10-07 : 04:15:45

sorry sorry!
it's all my fault!

I was performing on the wrong server!!

thank you for help it works perfectly!!! :) :)

GN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-10-07 : 04:40:43

I'm glad to hear (...to read)