CODEWITHSUNDEEP
mysql
Dan
Dan

Reputation: 21

MySQL count distinct always returning '1'

Trying to do a count distinct on a table however never seems to go above 1, stripping it right back to simplest form...

MariaDB [cwos]> SELECT count(distinct 'cr') FROM cr_import WHERE cr_import.com_on_date = '2017-03-22';
+----------------------+
| count(distinct 'cr') |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

MariaDB [cwos]> SELECT count('cr') FROM cr_import WHERE cr_import.com_on_date = '2017-03-22';
+-------------+
| count('cr') |
+-------------+
|         110 |
+-------------+
1 row in set (0.00 sec)

In this example both should be the same result, cr is a unique field so all 110 should be counted on distinct as you can see...

MariaDB [cwos]> SELECT cr FROM cr_import WHERE cr_import.com_on_date = '2017-03-22' limit 5;
+------------+
| cr         |
+------------+
| 8907246/17 |
| 9213268/17 |
| 9213270/17 |
| 9213271/17 |
| 9213273/17 |
+------------+
5 rows in set (0.00 sec)

Have I missed something simple? (Can't use count(cr) for actual queries as whilst cr is unique when joining with other tables I can end up with multiple duplicates hence using distinct)

Upvotes: 1

Views: 855

Answers (3)

Yacino
Yacino

Reputation: 665

You can do a distinct count as follows:

SELECT COUNT(DISTINCT column_name) FROM table_name;

So in your case, it should be like this:

SELECT COUNT(DISTINCT cr) FROM cr_import WHERE cr_import.com_on_date = '2017-03-22';

Upvotes: 0

haMzox
haMzox

Reputation: 2109

Don't use cr in quotes! Use:

Replace it with COUNT(DISTINCT cr)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

You are currently counting a string literal, because 'cr' is a string, not a column. Remove those quotes and everything should be fine:

SELECT COUNT(DISTINCT cr)
FROM cr_import
WHERE cr_import.com_on_date = '2017-03-22'

As for why the count was showing up as 1, the string literal you counted is the same for all records.

Upvotes: 2

Related Questions