I am currently playing in a Fantasy Football league and I’m attempting to use some stats to make some informed decisions for my team.

At the moment, I have a table with some historical player performance data, with each row of the table being one player’s output in one game, as below:

Player PassingYards PassingTDs Tom Brady 200 5 Deshaun Watson 350 3

My league has a specific points scoring tariff, which I aim to store in another table, as below:

Metric Points PassingYards 0.04 PassingTDs 4

From the two tables above, I aim to build a view that will calculate the points scored for each game, which would like the below:

Player Points Tom Brady 28 Deshaun Watson 26

[Where 28 is (200*0.04) + (5*4) and 26 is (350*0.04) + (3*4)]

My question is, how do I store the points tariff data? My data obviously has more than 2 metrics so I’d prefer not to type in the number manually for each of the metrics. I’m sure there’s an easy way to do it but I just can’t work it out. Thanks!

## Advertisement

## Answer

I think you want:

Player Metric Value Tom Brady PassingYards 200 Tom Brady PassingTDs 5 Deshaun Watson PassingYards 350 Deshaun Watson PassingTDs 3

and:

Metric Points PassingYards 0.04 PassingTDs 4

Then you can join the tables and aggregate:

select pm.player, sum(pm.value * m.points) from playermetrics pm join metrics m on pm.metric = m.metric group by pm.player;

Actually, I suspect that you also want a `Players`

table and a `MetricId`

column in the `metrics`

table. That way, you don’t need to repeat strings in different tables — something where typos can throw off your results.