Reputation: 1391

mysql count unique row values

TABLE quotation

id  clientid
1   25
2   25
3   25
4   25
5   26

How can I query how many different clients exist in TABLE quotation? I don't want duplicate entries to be counted more than once.

I need the answer to be 2, in 1 row, because the only non-duplicated entries are (25, 26).

Upvotes: 21

Answers (6)

Reputation: 11

We can achieve this by:

SELECT  COUNT(DISTINCT clientid) as Unique_Client FROM quotation;

The resulted output is 2 as per the requirement, because the unique client ID are 25 and 26

Upvotes: 0

Reputation: 1391

I find a way out

SELECT COUNT(*) as total FROM (SELECT COUNT(*) FROM quotation GROUP BY
clientid) t1

Upvotes: 4

Reputation: 71

If you want to count the total number of unique entries this will return a number in column count.

SELECT COUNT(*) as total FROM (SELECT COUNT(*) FROM quotation GROUP BY clientid having count(*) > 1) t1

If you want to count the total number of entries with duplicate rows then use the following mysql.

SELECT COUNT(*) as total FROM (SELECT COUNT(*) FROM quotation GROUP BY clientid having count(*) >= 2) t1

Upvotes: 3

Reputation: 10685

I tried the following on a MySQL 5.x database.

id is an integer and clientid is an integer. I populated with two rows:

id  clientid
1   25
2   25

This SQL query will print the rows that have exactly 2 elements:

select * from test1 group by clientid having count(*) = 2;

If you want 2 or more elements, replace = 2 in the example above with >= 2.

select * from test1 group by clientid having count(*) >= 2;

Upvotes: 2

Reputation: 3583

SELECT clientid, COUNT(clientid) FROM quotation 
GROUP BY clientid

Upvotes: 0

Reputation: 125486

select count(distinct clientid) from quotation

Upvotes: 52