ACCESS to MySQL: Query Conversion Help

mailto · #1 (**permalink**) 01-28-05, 15:12

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

r937 · #2 (**permalink**) 01-29-05, 01:06

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

Internet Services

Web Development

Digital Marketing

Consumer Tech