help with calculations

tjbarr

I have some calculations to make on a table with suppressed data. The suppressed data is represented as a "-1". I want to divide the wages by the employment and call it Z. If either are "-1" then I don't want an answer for Z, I just want the text "suppressed". So in the data below 1998 would be calculated. In 1998 Z would not be calculated for Columbus because of suppressed data in wages, and in 1999 Z would not be calculated for Columbus because of suppressed Employment. I am working with SQL in a PHP environment.

e.g. City variable 1997 1998 1999
Cleveland Employment 6,000 5,500 6000
Cleveland Wages 240,000 222,500 250,000
Columbus Employment 8,000 11,000 -1
Columbus Wages 180,000 -1 40,000

pete_bisby

Is this information all in one table, or is it separated?

You can either try and use an IF statement within your SQL query, asking:

IF(Wages<>-1 AND Employment<>-1, Wages/Employment,"suppressed") AS Z

Or you can create a loop within PHP and gather/query your information that way. Without any tables, though, it's a bit tricky trying to visualise you solution.

tjbarr

My code is an sql string.

$strSQL=$strSQL."((".$table.".".$pre.$yr_array[$j]."/".$table3.".".$pre.$yr_array[$j].
")* ".$table2.".".$pre2.$yr_array[$j].") as " .$newpre.$yr_array[$j].",";

drawmack

and the response you got used a sql if

tjbarr

Not sure how to code the if . I need to say only if denom is not = to zero else the answer is automatically zero.

drawmack

that's exactly what the if statement above does.

tjbarr

Yes but I get a postgres error. How do I write the sql code - from above with a variable called denom set to the sql code denominator and check its value?