CODEWITHSUNDEEP
mysqlsql
saurabh sisodia
saurabh sisodia

Reputation: 312

why given query is giving unexpected output?

Table in MySql database contains a column named department with values [HR,Admin,HR,Admin,Admin,Account,Account,Admin].

I want to fetch the unique values of department and print its length.

Query which I executed is:

SELECT CHAR_LENGTH(DISTINCT department) from table`

Which is giving error.

But if I execute:

SELECT DISTINCT CHAR_LENGTH(department) from table

It is giving the correct output.

I want to know why the first query is giving the error?

Upvotes: 1

Views: 24

Answers (1)

scragar
scragar

Reputation: 6824

SELECT
    CHAR_LENGTH(DISTINCT department)
FROM table

Is giving you an error because CHAR_LENGTH takes a single value, DISTINCT is used for taking multiple values and returning only unique occurrences of that value. Using DISTINCT inside CHAR_LENGTH implies that CHAR_LENGTH will need to handle multiple values which it can't do and as a result it's an error.

SELECT DISTINCT
    CHAR_LENGTH(department)
FROM table

Has a different meaning, here we perform CHAR_LENGTH on each row in the table, then get the unique values for said length.

If you want to get the length of unique departments you can use GROUP BY to group all the departments into a single row, then return the CHAR_LENGTH of the department once it's a single row there which will preserve instances where multiple departments have the same length(like English and Spanish):

SELECT
    CHAR_LENGTH(department)
FROM table
GROUP BY
    department

Upvotes: 2

Related Questions