Filtering data in mysql

Question

Filtering data in mysql

Navigation

#1 by (4 votes)

1

I have a table called tb_codigos 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 the records that have more than 1 character it gives error, theoretically should appear all records. Is it 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

asked by anonymous 23.04.2018 / 19:34

1 answer

Take part of the field value How to use AutoMapper 6.2.2 in Asp MVC5?

score 4 · Accepted Answer

You can not use aliases in a WHERE clause

Or you can use own query , which would look like this:

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 that I advise you to use, because you do not need to repeat the string count, it would look like this:

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;