CODEWITHSUNDEEP
sqlsql-servert-sql
Suzane
Suzane

Reputation: 203

Counting only number of rows a record being repeated

So here is the problem which i am facing- I have a table "A" as below

  Name     AmountPaid    Type
  NAME1        5          1
  NAME1        10         1
  NAME1        3          1
  NAME2        5          2
  NAME2        9          2
  NAME2        8          2

Here we have two names - Name1 and Name2 with type 1 and 2

I am trying to get a query which should return only number which should shows how many times collection of unique name exists in the table. Here in above i am expecting the result as - 2 I tried query 

Select NAME, count(Name) from A group by Name

however this will return below 2 records.

  NAME1    3
  NAME2    3

Any help is deeply appreciated.

Upvotes: 0

Views: 90

Answers (4)

CodeWithCoffee
CodeWithCoffee

Reputation: 1904

Please try below query:

Select count(distinct(NAME)) from A

Upvotes: 3

Vasily
Vasily

Reputation: 5782

use this:

--TEST TABLE
Declare @sometable as table (Name Varchar(10))
insert into @sometable
values  ('Name1'),
        ('Name1'),
        ('Name1'),
        ('Name2'),
        ('Name2'),
        ('Name2'),
        ('Name3'),
        ('Name4')
--VARIANT 1
SELECT COUNT(*)
FROM   (
            SELECT NAME,
                   COUNT(*) AS Cnt
            FROM   @sometable
            GROUP BY NAME
        ) AS T
WHERE  Cnt > 1
--VARIANT 2
SELECT COUNT(*)
FROM   ( SELECT NAME
         FROM   @sometable
         GROUP BY NAME
         HAVING COUNT(*) > 1 ) AS T
--VARIANT 3
SELECT COUNT(*)
FROM   (SELECT DISTINCT NAME, COUNT(*) OVER (PARTITION BY NAME) AS Cnt
        FROM   @sometable
        ) AS T
WHERE  Cnt > 1

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

If am not wrong this is what you need. Considering that you don't want to count the name if it is not duplicated

SELECT Count(DISTINCT NAME)
FROM   (SELECT *,
               Row_number()OVER(partition BY NAME ORDER BY type) rn
        FROM   Yourtable) a
WHERE  rn > 1

Upvotes: 2

Alan
Alan

Reputation: 3002

You can use DISTINCT to do this:

SELECT COUNT (DISTINCT NAME)
FROM A

Upvotes: 1

Related Questions