Search table with condition.

Discussion in 'MySQL' started by Jamie T, Jan 30, 2014.

0
  1. #1
    Hello. Does anyone know why this does not work? I am trying to call all records with a 'rate' of less than 8000.
    It works until I put in the 'rate' condition.
    Any help is much appreciated, thankyou.
    Jamie.

    select
ROUND ((mixact / TIMEDIFF(PROD.timemixfin,PROD.timemixstart)*6000)/COUNT(mixact),0) as rate
from PROD
where  'rate' < '8000' group by PROD.index ORDER BY PROD.time
    Code (markup):

     
    Last edited by a moderator: Feb 3, 2014
    Jamie T, Jan 30, 2014 IP
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    Loose the '' around rate, or replace them with ``
     
    PoPSiCLe, Jan 31, 2014 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    28,830
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #3
    Just to expand on @PoPSiCLe's answer

    quotes '' go around values
    ticks `` go around column names and table names

    so your query could look like this

    
select
ROUND ((`mixact` / TIMEDIFF(`PROD`.`timemixfin`, `PROD`.`timemixstart`)*6000)/COUNT(`mixact`),0) as `rate`
from PROD
where  `rate` < '8000' group by `PROD`.`index` ORDER BY `PROD`.`time`
    Code (markup):
    It's particularly handy when there is a chance that a table or column name could be confused with a MySQL reserved word.
     
    sarahk, Feb 3, 2014 IP
    ryan_uk likes this.
  4. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #4
    Which is easily avoided by not using column names that are reserved words...
     
    PoPSiCLe, Feb 5, 2014 IP
    sarahk likes this.
  5. Jamie T

    Jamie T Member

    Messages:
    22
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #5
    Thanks fella's, but that does not work. Getting the same problem. Say's "Unkown column 'rate' in where claus.

    Rate is not a column name, it's a name I put in the statement to identify the calculated 'rate' within the statemant.

    Any other clues please?

    Jamie.
     
    Jamie T, Feb 7, 2014 IP
  6. sarahk

    sarahk iTamer Staff

    Messages:
    28,830
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #6
    aaah, yes. try this
    select
ROUND ((`mixact` / TIMEDIFF(`PROD`.`timemixfin`, `PROD`.`timemixstart`)*6000)/COUNT(`mixact`),0) as `rate`
from PROD
group by `PROD`.`index` HAVING`rate` < '8000' ORDER BY `PROD`.`time`
    Code (markup):
     
    sarahk, Feb 8, 2014 IP
  7. Jamie T

    Jamie T Member

    Messages:
    22
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #7
    Thankyou Sarahk! That has done the trick. Never used the 'HAVING' condition before. Thanks posicle for trying also.
    Best Wishes.
    Jamie.
     
    Jamie T, Feb 11, 2014 IP