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.

    Code (Text):
    1. select
    2. ROUND ((mixact / TIMEDIFF(PROD.timemixfin,PROD.timemixstart)*6000)/COUNT(mixact),0) as rate
    3. from PROD
    4. where  'rate' < '8000' group by PROD.index ORDER BY PROD.time
    Last edited by a moderator: Feb 3, 2014
    Jamie T, Jan 30, 2014 IP
  2. PoPSiCLe

    PoPSiCLe Well-Known Member

    Messages:
    1,212
    Likes Received:
    108
    Best Answers:
    51
    Trophy Points:
    160
    #2
    Loose the '' around rate, or replace them with ``
    PoPSiCLe, Jan 31, 2014 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    17,405
    Likes Received:
    1,740
    Best Answers:
    37
    Trophy Points:
    510
    #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

    Code (Text):
    1.  
    2. select
    3. ROUND ((`mixact` / TIMEDIFF(`PROD`.`timemixfin`, `PROD`.`timemixstart`)*6000)/COUNT(`mixact`),0) as `rate`
    4. from PROD
    5. where  `rate` < '8000' group by `PROD`.`index` ORDER BY `PROD`.`time`
    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:
    1,212
    Likes Received:
    108
    Best Answers:
    51
    Trophy Points:
    160
    #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:
    16
    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:
    17,405
    Likes Received:
    1,740
    Best Answers:
    37
    Trophy Points:
    510
    #6
    aaah, yes. try this
    Code (Text):
    1. select
    2. ROUND ((`mixact` / TIMEDIFF(`PROD`.`timemixfin`, `PROD`.`timemixstart`)*6000)/COUNT(`mixact`),0) as `rate`
    3. from PROD
    4. group by `PROD`.`index` HAVING`rate` < '8000' ORDER BY `PROD`.`time`
    sarahk, Feb 8, 2014 IP
  7. Jamie T

    Jamie T Member

    Messages:
    16
    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