CODEWITHSUNDEEP
mysqlsqlsql-serversql-server-2008oracle11g
Brijesh Kushwaha
Brijesh Kushwaha

Reputation: 273

Return count along with result set

I want unique count of one column along with the result set .e.g if the result set is like this

              ID      Name
               2       A
               2       B
               4       C
               5       D
               1       A
               1       B

The i want return from database as :

             Count  ID   Name
              4      2    A
              4      2    B
              4      4    C
              4      5    D
              4      1    A
              4      1    B

Where you can clearly see the 4 is the unique count if ID column.

Upvotes: 0

Views: 271

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

For both MySQL and SQL Server, you can do this in a correlated subquyery like this:

SELECT
  (SELECT COUNT(DISTINCT Name)
   FROM tablename) AS 'Count',
  ID,
  Name
FROM tablename;

MySQL Fiddle Demo.

SQL Server Fiddle Demo.

Upvotes: 1

John Woo
John Woo

Reputation: 263803

This will work on almost all RDBMS.

SELECT  b.totalCount, a.ID, a.name 
FROM    tableName a 
        CROSS JOIN 
        (SELECT COUNT(DISTINCT Name) totalCount 
         FROM tableName) b

Upvotes: 1

Related Questions