CODEWITHSUNDEEP
mysqlsql
Roger Jarvis
Roger Jarvis

Reputation: 434

Counting unique numbers in a column MySQL

I have a query that returns data in the following format:

id | name | number
 1   John    12545
 1   John    50496
 2   Mary    23443
 3   Mark    54
 3   Mark    5600
 3   Mark    50206

I would like to find out the number of distinct ids that appear in the result set. For example, for the result above. I would like to obtain the value 3.

Is there any way to add a column so the result looks like this instead?

count | id | name | number
 3      1   John    12545
 3      1   John    50496
 3      2   Mary    23443
 3      3   Mark    54
 3      3   Mark    5600
 3      3   Mark    50206

My query is:

 SELECT * FROM (
    SELECT id FROM tableA
    WHERE xyz
 ) as t1
 JOIN tableB using (id)

Upvotes: 0

Views: 159

Answers (4)

L.Grillo
L.Grillo

Reputation: 981

SELECT COUNT(id) AS count , id, name, number 
FROM 
( 
    SELECT id 
    FROM tableA 
    WHERE xyz 
) as t1 
JOIN tableB using (id)
GROUP BY id, name, number

Upvotes: 0

Francis P
Francis P

Reputation: 13655

SELECT COUNT(DISTINCT id) would be faster than using column name.

SELECT (SELECT COUNT(DISTINCT id) FROM tableName) as 'count',
        id,name,number  
FROM tableName

Upvotes: 0

Weacked
Weacked

Reputation: 970

You should try :

SELECT id,name,number, (SELECT COUNT(DISTINCT name) FROM YourTableName) FROM YourTableName

Good luck

Upvotes: 0

John Woo
John Woo

Reputation: 263723

SELECT (SELECT COUNT(DISTINCT id) FROM tableName) totalCount,
       id,name,number
FROM tableName

or by using CROSS JOIN

SELECT x.totalCount,
       a.id, a.name, a.number
FROM tableName a, (SELECT COUNT(DISTINCT id) totalCount 
                   FROM tableName) x

Upvotes: 4

Related Questions