Results 1 to 6 of 6

Thread: Counting rows

  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: 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?

  2. #2
    Join Date
    Mar 2004
    Posts
    28

    Re: Counting rows

    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?
    just an analyst......

  3. #3
    Join Date
    Feb 2004
    Posts
    492

    Re: Counting rows

    My version of mssql does not allow table aliasses on updates.

  4. #4
    Join Date
    Jul 2003
    Posts
    123
    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)

  5. #5
    Join Date
    Jul 2003
    Posts
    123
    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

  6. #6
    Join Date
    Mar 2004
    Posts
    28
    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
    just an analyst......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •