Count total without duplicate using MySQL and PHP

Question

I am attempting to query a MySQL database with PHP to count the total number of entries without duplicates.

EXAMPLE
N111US
N111US
N123US
N345US
A6-EWD
A7-EEE
B-18701
N123US
N345AA

I wish to have the total number of the unique entries, and I cannot begin to see where to start with the SQL query, that is, Total Returned: 7

add in the end of query group by coloumn

M Khalid Junaid
– M Khalid Junaid

2013-04-15 19:59:18 +00:00
Commented Apr 15, 2013 at 19:59 — M Khalid Junaid
– M Khalid Junaid, Commented Apr 15, 2013 at 19:59

Sebas · Accepted Answer · 2016-01-24 22:00:52Z

20

This DISTINCT in this query:

SELECT COUNT(DISTINCT `column`) AS `cnt` FROM `table`;

will eliminate duplicates.

Then, from php, fetch the cnt column value the way you would do for any other regular query.

edited Jan 24, 2016 at 22:00

answered Apr 15, 2013 at 20:04

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

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

2 Comments

cpugourou Over a year ago

This solution works but count distinct is an abomination in terms of performance. Array_unique in php7 is a blast.

Michael K Over a year ago

I'll just add that query duration for me using COUNT(DISTINCT xxx, yyy) was basically the same as for a query that only used DISTINCT to show all entries. Actually the COUNT(DISTINCT xxx, yyy) on was a tiny bit faster (.312 sec vs .375 sec)

Andreas3204 · Accepted Answer · 2013-04-15 20:13:24Z

1

I like the SQL answers posted above. They're efficient and performed on the query, itself. But if you have already read the items from the database into an array, you can use PHP's array_unique--then get a count of that result into an array with count. For example:

<?php
$input = array("a" => "dog", "cat", "b" => "cat", "dog", "mouse");
$result = array_unique($input);
$uniqueCount = count($result);
?>

Hope this helps. :)

answered Apr 15, 2013 at 20:13

Andreas3204

1421 silver badge9 bronze badges

Collectives™ on Stack Overflow

Count total without duplicate using MySQL and PHP

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related