Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    3

    Question Unanswered: Finding number of repetition T-SQL

    I have two tables.

    Table A has a1, a2 and a3 fields[columns].
    Table B has b1, and b2 fields[columns].

    Columns a1 and b1 are related.

    Column a1 has 1000 rows while b1 has 600 rows.
    {The number of rows like 1000 and 600 are just examples, I can find the number of rows for Table A and B using the function "COUNT", but this may/not be needed!}

    Column b1 has distinct records while a1 doesn't.

    Objective:
    I want to find out the number of repetition of each values in b1 which are repeated in a1 and store it in a new Table C which will have the following columns b1, b2 and NumberOfRepetition.

    Note: Not all values of b1 are in a1, some rows of C.NumberOfRepetition can be zero.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT	 <FieldName>
    	,Count(<FieldName>)
    FROM	<MyTable>
    GROUP BY <FieldName>
    EDIT: This has no join, so only displays results from MyTable (does not include blank rows from any other table)
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    SELECT B.b1, COUNT(A.A1)
    FROM B LEFT JOIN A ON B.B1=A.A1
    GROUP BY B.B1

    Returns all 600 rows from B, and a count of matching rows in A.
    Will show zero if no match is found.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Mar 2007
    Posts
    3
    Thank you very much pals/folks.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by RedNeckGeek
    Code:
    SELECT B.b1, COUNT(A.A1) AS count
    FROM B LEFT JOIN A ON B.B1=A.A1
    GROUP BY B.B1
    Returns all 600 rows from B, and a count of matching rows in A.
    Will show zero if no match is found.
    And if you want to exclude result rows with 0 count, you can either nest the above query into a subselect and filter with "WHERE count > 0", or you add "HAVING COUNT(*) > 0".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Feb 2011
    Posts
    1

    Arrow

    pls try like this one

    SELECT fieldname,
    COUNT(fieldname) AS NumOccurrences
    FROM tablename GROUP BY fieldname
    HAVING ( COUNT(fieldname) > 1 )

Posting Permissions

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