Filtering data in mysql

Asked

Viewed 121 times

1

I have a table called tb_codes containing the following columns: Cod,txt1,txt2

I need to add the number of characters in the two columns txt1 and txt2, in the example below the character size is 6, when I try to filter all records that have more than 1 character it gives error, theoretically should appear all records. Would be some syntax error?

Example:

cod     txt1    txt2  tamanho
1       abc     abc   6
2       abc     abc   6
3       abc     abc   6
4       abc     abc   6

Sql:

SELECT
cod,
txt1,
txt2
COALESCE (sum(
character_length(txt1)+
character_length(txt2)+
)) AS 'tamanho' 
FROM tb_codigos 
where tamanho > 1
GROUP BY cod
order by tamanho desc
mysql sql
  • 2

    You can’t make a WHERE with an alias, use the HAVING Dust after the GROUP BY. Remove the WHERE and leave like this GROUP BY cod HAVING tamanho > 1 ORDER BY tamanho DESC

  • Now it worked, I didn’t know that Where alias didn’t work

  • @arllondias suggest you put this as an answer, or someone will do it and earn your credits :P

  • 1

    @rLines done =D

1 answer

4


You may not use alias in a clause WHERE

Or you can use your own query, that would look that way:

SELECT
  cod,
  txt1,
  txt2
  COALESCE (sum(
              character_length(txt1)+
              character_length(txt2)
            )) AS tamanho 
FROM tb_codigos 
where COALESCE (sum(
              character_length(txt1)+
              character_length(txt2)
            )) > 1
GROUP BY cod
ORDER BY tamanho desc;

Or using the HAVING which I advise you to use, as you do not need to repeat the string count, it would look that way:

SELECT
      cod,
      txt1,
      txt2
      COALESCE (sum(
                  character_length(txt1)+
                  character_length(txt2)
                )) AS tamanho 
    FROM tb_codigos 
    GROUP BY cod
    HAVING tamanho > 1
    ORDER BY tamanho desc;

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.