21

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).

Improve this question
2
  • 1
    Your title is misleading: you are not counting the duplicate rows, but the unique number of rows. Counting duplicate rows would return "4" since there are 4 rows which have the same value for clientid. Commented Nov 9, 2010 at 9:09
  • Proposed an edit to the question title/text, since this question is currently the top google hit for "mysql count duplicate entries". Commented May 13, 2013 at 22:16

6 Answers 6

Reset to default
52 
select count(distinct clientid) from quotation

read more

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

4

I find a way out

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

Comments

3

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
Improve this answer

Comments

2

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;
Improve this answer

Comments

0 
SELECT clientid, COUNT(clientid) FROM quotation 
GROUP BY clientid
Improve this answer

1 Comment

This does not get the total count for client like: "select count(distinct clientid) from quotation"
0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.