Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    1

    Exclamation Unanswered: ACCESS to MySQL: Query Conversion Help

    I have an ACCESS query that I am trying to convert to MySQL. See if you can help:

    ACCESS Query-->
    $query1 = odbc_exec($odbc1, "SELECT [CONCAT], IIF(Sum([LMU])>0,Format(100*Sum([LM])/Sum([LMU]),'000.0'),Format(100*0,'000.0')) AS [QUERY_LM] FROM [table1] WHERE [PLACE] LIKE '$place%' GROUP BY [CONCAT] ORDER BY [CONCAT] ASC") or die (odbc_errormsg());

    Possible MySQL Query [d/NOT work??]-->
    $query1 = "SELECT CONCAT, IF(SUM(LMU)>0,100*SUM(LM)/Sum(LMU),100*0) AS QUERY_LM FROM `table1` WHERE PLACE LIKE '$place%' GROUP BY CONCAT ORDER BY CONCAT ASC";
    $result1 = mysql_query($query1) or die("Error. " . mysql_error());

    As you may notice, I have not used the equivalent of FORMAT() in MySQL to simply this conversion, but it still doesn't work.

    --Mailto

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    CONCAT is a the name of a function in mysql, so you have to backtick it
    Code:
    select `CONCAT`
         , case when Sum(LMU) > 0
                then 100.0*Sum(LM)/Sum(LMU)
                else 0 
             end        as QUERY_LM 
      from table1 
     where PLACE like '$place%' 
    group 
        by `CONCAT` 
    order 
        by `CONCAT` asc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •