dcsimg
Results 1 to 4 of 4

Thread: Query with number of occurances.

  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Query with number of occurances.

    Hi there, had a quick question about finding the number of occurrences of specific column. Let me give a quick example.

    Table t:
    col_1 | col2 | col_3
    A | B
    A | C
    A | C
    A | D
    A | D
    A | D

    I want to put the number of occurrences of col_2 in a col_2.
    should output:

    col_1 | col2 | col_3
    A | B | 1
    A | C | 2
    A | C | 2
    A | D | 3
    A | D | 3
    A | D | 3

    Any ideas what I can do for this?
    Thank you very much.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    select col_1, COUNT(col_1) as col2 into #temp from tablet group by col_1
    update Tablet set col2 = a.col2 from tablet join #temp a on tablet.col1 = a.col1

  3. #3
    Join Date
    Mar 2011
    Posts
    2
    Wow thanks a lot. I know it's simple but I was using the count wrong.
    Much appreciated

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Happy SQLing!

Posting Permissions

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