locked
How to count same record in a table RRS feed

  • Question

  • i have :
    table  A: id - name - code

    but now i have so much same record by name, i want select all record by name have same name > 2, and count it . pls help me

    Friday, October 24, 2014 6:57 AM

Answers

  • Thanh;

    I don't know whether I've got you right, but the following SQL statement might solve your problem:

    SELECT COUNT(*), [name] FROM [A] GROUP BY [name] HAVING COUNT(*) > 2

    Regards,

    Andreas

    • Proposed as answer by Wendy Fu Saturday, October 25, 2014 2:00 AM
    • Marked as answer by Katherine Xiong Monday, November 10, 2014 7:06 AM
    Friday, October 24, 2014 7:10 AM
  • Hello,

    Group the data by Name and filter in the having clause

    SELECT Name, COUNT(*) AS Cnt
    FROM yourTable
    GROUP BY Name
    HAVING COUNT(*) > 1


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Wendy Fu Saturday, October 25, 2014 2:01 AM
    • Marked as answer by Katherine Xiong Monday, November 10, 2014 7:06 AM
    Friday, October 24, 2014 7:10 AM
  • SELECT *
    FROM Table t
    WHERE EXISTS (SELECT 1
    FROM table 
    WHERE name = t.name
    GROUP BY name
    HAVING COUNT(*) > 1)

    if you want names with counts alone use

    SELECT Name,COUNT(*)
    FROM table
    GROUP BY Name
    HAVING COUNT(*) >=2 


    Please Mark This As Answer if it solved your issue Please Mark This As Helpful if it helps to solve your issue Visakh ---------------------------- http://social.technet.microsoft.com/wiki/contents/articles/27020.user-page-visakh16.aspx http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Friday, October 24, 2014 7:23 AM
    • Proposed as answer by Wendy Fu Saturday, October 25, 2014 2:03 AM
    • Marked as answer by Katherine Xiong Monday, November 10, 2014 7:06 AM
    Friday, October 24, 2014 7:22 AM

All replies

  • Try:

    WITH CTE AS (
    SELECT Color, COUNT(*) OVER (PARTITION BY Color) AS Freq, 
    ROW_NUMBER() OVER (PARTITION BY Color ORDER BY (SELECT 1) ) AS RN
    FROM Production.Product WHERE Color is not null)
    SELECT Color, Freq FROM CTE WHERE RN=1
    AND Freq > 1 ORDER BY Color;
    /*
    Black	93
    Blue	26
    Multi	8
    Red	38
    Silver	43
    Silver/Black	7
    White	4
    Yellow	36
    */




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Friday, October 24, 2014 7:08 AM
  • Thanh;

    I don't know whether I've got you right, but the following SQL statement might solve your problem:

    SELECT COUNT(*), [name] FROM [A] GROUP BY [name] HAVING COUNT(*) > 2

    Regards,

    Andreas

    • Proposed as answer by Wendy Fu Saturday, October 25, 2014 2:00 AM
    • Marked as answer by Katherine Xiong Monday, November 10, 2014 7:06 AM
    Friday, October 24, 2014 7:10 AM
  • Hello,

    Group the data by Name and filter in the having clause

    SELECT Name, COUNT(*) AS Cnt
    FROM yourTable
    GROUP BY Name
    HAVING COUNT(*) > 1


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Wendy Fu Saturday, October 25, 2014 2:01 AM
    • Marked as answer by Katherine Xiong Monday, November 10, 2014 7:06 AM
    Friday, October 24, 2014 7:10 AM
  • SELECT *
    FROM Table t
    WHERE EXISTS (SELECT 1
    FROM table 
    WHERE name = t.name
    GROUP BY name
    HAVING COUNT(*) > 1)

    if you want names with counts alone use

    SELECT Name,COUNT(*)
    FROM table
    GROUP BY Name
    HAVING COUNT(*) >=2 


    Please Mark This As Answer if it solved your issue Please Mark This As Helpful if it helps to solve your issue Visakh ---------------------------- http://social.technet.microsoft.com/wiki/contents/articles/27020.user-page-visakh16.aspx http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Friday, October 24, 2014 7:23 AM
    • Proposed as answer by Wendy Fu Saturday, October 25, 2014 2:03 AM
    • Marked as answer by Katherine Xiong Monday, November 10, 2014 7:06 AM
    Friday, October 24, 2014 7:22 AM
  • Are you sure you want > 2 or just > 1 ?

    SELECT NAME, COUNT(*) AS TotalFound
    FROM TABLE
    GROUP BY NAME
    HAVING COUNT(*)>1


    If you also want the ID's and CODE's of the multiple records you can use this:

    SELECT * FROM 
    TABLE WHERE NAME IN (
    SELECT NAME
    FROM TABLE
    GROUP BY NAME
    HAVING COUNT(*)>1
    ) X 


    "If there's nothing wrong with me, maybe there's something wrong with the universe!"


    • Edited by cnk_gr Friday, October 24, 2014 8:52 AM
    Friday, October 24, 2014 8:51 AM