Counting rows

Hi,

I have a temp table that I use to calculate prices from and I need to know how many of each row with the same serialnumber.

I figured I could add a column that contains that number. But how will I get it there?

UPDATE [_temp] T1
SET T1.SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T1.SERIAL = T2.SERIAL)

Doesn't work. Any ideas?that should work.

I think we're both missing something really obvious...

I don't start my temp table names with the _ symbol...

I usually use the # sign.

Originally posted by oneleg_theone
Hi,

I have a temp table that I use to calculate prices from and I need to know how many of each row with the same serialnumber.

I figured I could add a column that contains that number. But how will I get it there?

UPDATE [_temp] T1
SET T1.SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T1.SERIAL = T2.SERIAL)

Doesn't work. Any ideas?|||My version of mssql does not allow table aliasses on updates.|||The tables names are example only, they are named something else. Too bad i named them "temp" in this example...

I already have some sql in a sp that updates tables using aliases so it's not that either... I thought. The first table must be without alias, then it works! But delivers the wrong result... It gives me total rowcount not the number of rows with the same serial..

Working query delivering wrong result:

UPDATE [_temp]
SET SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE SERIAL = T2.SERIAL)|||Trial and error provided the answer...

UPDATE [_temp]
SET SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T2.SERIAL = [_temp].SERIAL)

Now gives me the result I want.. :-D|||dang.

like I said, something obvious - can't use alias name on update.

Originally posted by oneleg_theone
Trial and error provided the answer...

UPDATE [_temp]
SET SERIAL_COUNT = (SELECT COUNT(*) FROM [_temp] T2 WHERE T2.SERIAL = [_temp].SERIAL)

Now gives me the result I want.. :-D