In SQL, Separating a value by its average value

Question

I am trying to separate a computed field (in an example that it is called CALC_TIPS) by the average value for this computed field specified by the specific value COMM_TYPE_ID. With this query, I can extract the AVG for each COMM_TYPE_ID.

SELECT AVG(CALC_TIPS), COMM_TYPE_ID
FROM (SELECT tips_amount/COMMUNICATIONS_ID AS CALC_TIPS, COMM_TYPE_ID
FROM consumer_action_log) AS cal
GROUP BY COMM_TYPE_ID;

How can I then return and divide each value in the CALC_TIPS field by the average value for its COMM_TYPE_ID? I am now at a loss. Thanks in advance!

Note. I work with MySQL. Also Note: I know that a subquery is not needed. I use this request as a proxy for a more complex request, with which I have to do the same.

Answer 1

, COMMS_TYPE_ID. , .

SELECT tips_amount/COMMUNICATIONS_ID/NULLIF(AVG_TIP,0)
FROM consumer_action_log A   # return to table consumer_action_log
INNER JOIN (
    SELECT AVG(CALC_TIPS) AVG_TIP, COMM_TYPE_ID
    FROM (
        SELECT tips_amount/COMMUNICATIONS_ID AS CALC_TIPS, COMM_TYPE_ID
        FROM consumer_action_log) AS cal
    GROUP BY COMM_TYPE_ID
) B ON A.COMM_TYPE_ID = B.COMM_TYPE_ID

NULLIF 0 → NULL, .

Answer 2

:

select cal1.calc_tips/cal2.avgCalc_Tips as theAnswer
  from (Select tips_amount/communication_id as calc_tips
             , comm_type_id
          from consumer_action_log)  cal1
  JOIN (select avg(tips_amount/communication_id) as avgCalcTips
             , comm_type_id
          from consumer_action_log
         group by comm_type_id    
          from consumer_action_log)  cal2
    ON cal1.comm_type_id = cal2.comm_type_id

EDIT: , message_id :)