Get unique values of a field and prints its length

Question

Table Name: Worker,

Fields : worker_id | first_name | last_name | department

I have a table name worker and i wanted to write an SQL query that fetches the unique values of DEPARTMENT from Worker table and prints its length. So i tried running this : (Database- Mysql)

select length(distinct(department)) from worker;

But it is giving an error saying:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct(department)) from worker' at line 1

But when i ran below query, it works perfectly fine:

select distinct(length(department)) from worker;

Can somebody please explain me why interchanging distinct and length function makes query works?

Thanks in advance!

Answer 1

Try not to use distinct like function but clause otherwise it will give syntax error.

Below sql statement will execute as shown below:

select distinct (length('xyz'))  ---- length('xyz') : 3
select distinct (3)  ---- output : 3

Answer 2

Distinct is not properly a function but a clause

 select  distinct length(department) from worker;

Anyway in MySQL work also with function syntax

  select  distinct( length(department)) from worker;

The code with the exchanged token don't work because DISTINCT produce an aggregated result removing the duplicated values,this implies that the outer length() function work on not correct set of rows or better the db engine see that there an improper use of the DISTINCT clause and raise the syntax error

select length( distinct 'A'  )  this raise an error 

If you want use the outer length() function you should code this way

select length(my_col) from (
 select distinct department  my_col from worker
) ;