How to add a new calculated field by using `hook_views_query_alter`?

Question

I have three node types that I have created relations between using the Relation module. Seaon --> Player --> Week

The player node has a numeric field called cost. The relationship between the player and the week has a numeric field called score.

The following is a views query (with some alias changes to make it a tad more intelligible) that I use to summarise data per season. The view works fine but I need to add a calculated field that takes the sum of the weekly scores and divides it by the player cost.

SELECT  PLAYER.title AS PLAYER_title
        ,PLAYER.nid AS PLAYER_nid
        ,PLAYER_COST.field_cost_value AS PLAYER_COST_field_cost_value
        ,MIN(PLAYER.nid) AS PLAYER_nid_1
        ,'node' AS field_data_field_cost_node_entity_type
        ,MIN(PLAYER2WEEK.rid) AS PLAYER2WEEK_rid
        ,'relation' AS field_data_field_score_relation_entity_type
        ,SUM(PLAYER2WEEK__field_data_field_score.field_score_value) AS PLAYER2WEEK__field_data_field_score_field_score_value
FROM    {node} SEASON
INNER JOIN 
        {field_data_endpoints} FDE 
ON      node.nid = FDE.endpoints_entity_id 
AND     (FDE.bundle = 'player_in_season' 
AND     FDE.endpoints_entity_type = 'node' 
AND     FDE.endpoints_r_index = '1')
INNER JOIN 
        {field_data_endpoints} FDE2 
ON      FDE.entity_id = FDE2.entity_id 
AND     (FDE2.endpoints_entity_type = 'node' 
AND     FDE2.endpoints_r_index !=  FDE.endpoints_r_index 
AND     1 = '1')
INNER JOIN 
        {node} PLAYER 
ON      FDE2.endpoints_entity_id = PLAYER.nid 
AND     FDE2.endpoints_entity_type = 'node'
LEFT JOIN 
        {field_data_endpoints} PLAYER_FDE 
ON      PLAYER.nid = PLAYER_FDE.endpoints_entity_id 
AND     (PLAYER_FDE.bundle = 'has_weekly_score' 
AND     PLAYER_FDE.endpoints_entity_type = 'node' 
AND     PLAYER_FDE.endpoints_r_index = '0')
LEFT JOIN 
        {relation} PLAYER2WEEK 
ON      PLAYER_FDE.entity_id = PLAYER2WEEK.rid
LEFT JOIN 
        {field_data_field_cost} PLAYER_COST 
ON      PLAYER.nid = PLAYER_COST.entity_id 
AND     (PLAYER_COST.entity_type = 'node' 
AND     PLAYER_COST.deleted = '0')
LEFT JOIN 
        {field_data_field_score} PLAYER2WEEK__field_data_field_score 
ON      PLAYER2WEEK.rid = PLAYER2WEEK__field_data_field_score.entity_id 
AND     (PLAYER2WEEK__field_data_field_score.entity_type = 'relation' 
AND     PLAYER2WEEK__field_data_field_score.deleted = '0')
WHERE   (( (node.nid = '3' ) )
AND     (( (node.type IN  ('SEASON')) AND (node.status = '1') )))

From all the reasearch I've done, it looks like the correct option is to use hook_views_query_alter() to add the new field. This is where I lose it, I have no idea how to write the code to build the new field. Essentialy, based on the query above the field needs to be

SUM(PLAYER2WEEK__field_data_field_score.field_score_value) / PLAYER_COST.field_cost_value AS new_field

I just need some help in getting strated on the code in my custom module

This answer seems to meet my requirements conceptually, I am just lost on how to construct the function.

Answer 1

Turns out that I didn't need the hook at all. I was able to achieve the result by using a Math Expression within the views module