1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Search table with condition.

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

  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 Well-Known Member

    Messages:
    2,021
    Likes Received:
    197
    Best Answers:
    78
    Trophy Points:
    185
    #2
    Loose the '' around rate, or replace them with ``
     
    PoPSiCLe, Jan 31, 2014 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    18,892
    Likes Received:
    2,237
    Best Answers:
    54
    Trophy Points:
    615
    #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 Well-Known Member

    Messages:
    2,021
    Likes Received:
    197
    Best Answers:
    78
    Trophy Points:
    185
    #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:
    19
    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:
    18,892
    Likes Received:
    2,237
    Best Answers:
    54
    Trophy Points:
    615
    #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:
    19
    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