Help needed designing MySQL query.

Penguin_King

As the subject line says, I'm a bit new at this databasing stuff, and I'm not quite sure how best to structure a particular query I need to do.

The structure of the relevant portion of the records I want to work with is as follows:

An integer "flag", which can equal 0 or 1.

An integer "total0", which can have any positive value.

An integer "total1", which can have any positive value.

I want my result set to contain ten values in descending order of a calculated value we'll call "percent", where:

percent = total0 / (total0 + total1) if flag == 0
percent = total1 / (total0 + total1) if flag == 1

How would I go about writing this query in MySQL 4.0.15?

tekky

Originally posted by Penguin King
As the subject line says, I'm a bit new at this databasing stuff, and I'm not quite sure how best to structure a particular query I need to do.

The structure of the relevant portion of the records I want to work with is as follows:

An integer "flag", which can equal 0 or 1.

An integer "total0", which can have any positive value.

An integer "total1", which can have any positive value.

I want my result set to contain ten values in descending order of a calculated value we'll call "percent", where:

percent = total0 / (total0 + total1) if flag == 0
percent = total1 / (total0 + total1) if flag == 1

How would I go about writing this query in MySQL 4.0.15?

well to answer the first parts, I would use TINYINT null NO, default 0
INT/BIGINT (dependingon how big of #) unsigned (means no negs)
same as last one for total1
then a select somewhat like

SELECT flag, total0, total1, (total0/(total0+total1)) AS percent0, (total1/(total0+total1)) AS percent1 FROM yourTable

I realize this isnt quite what you wanted.. but I dont know a way to do it in ONE Sql query (not aware of any "param switching" functions in mySQL that could let you switch thingsin 1 statement call based on a value

this would return

$row['flag'] $row['total0'] $row['total1'] $row['percent0'] $row['percent1']

granted that is if you do $row = mysql_fetch_array($result)

otherwise the variable name would be whatever you used...