CODEWITHSUNDEEP
mysql
ToujouAya
ToujouAya

Reputation: 593

Mysql Why select count distinct return zero

I would like to know why select count distinct return zero result. I have also checked various answers at here but it's not answer for my case. MySQL version 5.6 Link to check http://sqlfiddle.com/#!9/276302/3/0

Sample schema:

CREATE TABLE IF NOT EXISTS `employees` (
  `id` int(6) unsigned NOT NULL,
  `name` varchar(3) NOT NULL,
  `salary` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `employees` (`id`, `name`, `salary`) VALUES
  ('1', 'a', 6),
  ('2', 'b', 5),
  ('3', 'c', 5),
  ('4', 'd', 4);

Query:

SELECT COUNT(DISTINCT(salary))
FROM employees;

Guys, do you have any idea ?

Upvotes: 3

Views: 673

Answers (5)

Michel de Ruiter
Michel de Ruiter

Reputation: 7954

This is a known and long-standing bug in MySQL. :-(

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

In my opinion it is a bug of this specific version of MySQL 5.6.

But it will work for MariaDB or MySQL 5.7

The other answers suggest that it is the problem with datatype.But if we remove DISTINCT:

SELECT COUNT(salary)
FROM employees;
-- 4

DBFiddle Demo MySQL 5.6

Upvotes: 2

godot
godot

Reputation: 3545

select count(ds) from (SELECT distinct(salary) as ds
FROM employees) as s;

Upvotes: 1

sherry
sherry

Reputation: 13

you need to change the datatype of salary from varchar to int. OR you need to include salary values in " ".

Upvotes: 0

nitsram
nitsram

Reputation: 716

Your salary column is type VARCHAR, try using INT or another appropriate data type.

See here: http://sqlfiddle.com/#!9/875bde/1/0

Upvotes: -2

Related Questions