Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Unanswered: Query Duplicate column value row count

    How do I count the number of alike values in a column and have that value as a count column. Example using Col3:
    Col1 Col2 Col3
    A 12 Test
    B 45 Test1
    C 45 Test
    D 12 Test
    E 10 Test1
    F 11 Test2

    Result
    Col1 Col2 Col3 Count
    A 12 Test 3
    B 45 Test1 2
    C 45 Test 3
    D 12 Test 3
    E 10 Test1 2
    F 11 Test2 1

    Thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    select *, COUNT(*) OVER(PARTITION BY Col3) as MyCount
    from MyTable
    Hope this helps.

  3. #3
    Join Date
    Sep 2012
    Posts
    4
    Thanks for the quick response. Is it possible to expand it to also count alike values in a similar column in another table without showing records from 2nd table

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    What is the relationship of this other table with the first table?
    Maybe you should post a table structure as an example.

  5. #5
    Join Date
    Sep 2012
    Posts
    4
    2nd table, some but not all matching columns
    ColA ColB, Col3 (same as Col3 in 1st table)
    A B Test1
    C D Test

    revised result (reference earlier post)
    Col1 Col2 Col3 Count
    A 12 Test 4
    B 45 Test1 3
    C 45 Test 4
    D 12 Test 4
    E 10 Test1 3
    F 11 Test2 1

    There will be similar where conditions applied to both tables

  6. #6
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with CTE as
    (
        select Col3, COUNT(*) as MyCount
        from OtherTable
        group by Col3
    )
    
    select 
        m.*, 
        COUNT(m.Col1) OVER(PARTITION BY m.Col3) + COALESCE(c.MyCount, 0) as MyCount
    from MyTable as m
    left join CTE as c on c.Col3 = m.Col3
    Hope this helps.

  7. #7
    Join Date
    Sep 2012
    Posts
    4
    Thank you, works as requested

Posting Permissions

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