James Madison - 11 months ago 50

MySQL Question

I am not sure if this can be done in one swoop, but I think it would be nice.

I need to calculate the following

(ORank×60%)+(CRank×40%)=PScore

(BRank×60%)+(ARank×40%)=NScore

PScore−NScore=CScore

CScore is my ultimate need. But I need to use the data found in one table to give me this results.

I know that this can be done one by one but not advanced enough in my skill set to know if this can be done at once.

Answer

I'm hoping this isn't deceptively simple...

I assume that these ranks are columns and not calculated. if so, simple algebraic substitution says that:

```
CScore = (((ORank*.6)+(CRank*.4))-((BRank*.6)+(ARank*.4)))
```

So...

```
SELECT (((ORank*.6)+(CRank*.4))-((BRank*.6)+(ARank*.4))) AS CScore
FROM table...
```

If the ranks are instead calculated and you don't care about performance, you could do (nasty-looking) subselects or joins.

Source (Stackoverflow)