CODEWITHSUNDEEP
sqlpostgresql
CodyBugstein
CodyBugstein

Reputation: 23362

SQL expression that adds a column from arithmetic on two other columns

I have a table:

_________________________________
| Student | Competitions | Wins |
_________________________________
| Johnny  |     8        |  4   |
| Michael |     9        |  4   |
| Ariel   |     5        |  3   |
| Raphael |     5        |  1   |

I would like to create a new table that adds a new column containing the win percentage of each student. Like this:

__________________________________________
| Student | Competitions | Wins | Win Pct |
__________________________________________
| Johnny  |     8        |  4   |   .5    |
| Michael |     9        |  4   |   .44   |
| Ariel   |     5        |  3   |   .6    |
| Raphael |     5        |  1   |   .2    |

Is there any way to do this - i.e. to create a new column by dividing the value in one column by the value in another?

Upvotes: 0

Views: 1653

Answers (4)

SamShin
SamShin

Reputation: 11

mti2935 is right, the only change is adding ' * 1.0' this will convert int to float

SELECT Student, Competitions, Wins, Wins * 1.0 / Competitions FROM tablename

Upvotes: 1

matt.bungard
matt.bungard

Reputation: 109

I'm going to agree with the other answers, that this is a bad design practice as you're storing basic calculated data.

That being said..this query should do it for you (assuming, you've already created the column)

update tablename set WinPct = (coalesce(Wins,0)/Competitions) where Competitions IS NOT NULL

This should cleanly support null values (NULL competitions => Null WinPct).

You'd have to set triggers (or use application logic) to repopulate this field everytime Wins or Comps are updated, but I'll skip that part of it. This is another reason why storing this is a bad idea.

Upvotes: 0

mgw854
mgw854

Reputation: 677

Conceptually, what you are doing is bad. A database should store basic information--here, data like Student, Competitions, and Wins. A win percentage, on the other hand, can change based on the other data. It's derived from both Competitions and Wins, and so should be computed every time it's needed. There are, of course, performance considerations for especially complex derived columns, but this shouldn't be a concern in your case.

For a rough analogy, think about why storing a user's age in a database is a poor design decision when you already have their birth date.

Upvotes: 0

mti2935
mti2935

Reputation: 12037

How about just adding an expression as another field in your SELECT clause, like so:

SELECT Student, Competitions, Wins, Wins / Competitions 
FROM tablename

Upvotes: 3

Related Questions