Calculate the average on runtime from the query in laravel

Question

I want to calculate the average of daily gc_score obtained from the below query:

$query = DB::table('qc_feedbacks')
    ->selectRaw("
        COUNT(cru_agent_detail_id) as numberofEvaluations,
        COUNT( CASE WHEN qc_feedbacks.status='Passed' Then 1 ELSE Null End) as passed,
        COUNT( CASE WHEN qc_feedbacks.status!='Passed' Then 1 ELSE Null End) as failed,
        AVG(score) as gc_score,
        cru_agent_details.cru_id as cru_id,
        cru_agent_details.name as agentName,
        cru_agent_details.team_lead as team_lead,
        evaulated_by,
        cru_agent_detail_id,
        audit_date
    ")
    ->join('cru_agent_details', 'cru_agent_details.id', '=', 'qc_feedbacks.cru_agent_detail_id')
    ->groupBy('audit_date', 'cru_agent_detail_id')
    ->orderBy('audit_date', 'desc');

The average of daily gc_score obtained from the above query is our monthly gc_score. The above query should be displaying month wise gc_score for the agent by averaging the gc_score from the above query and combine it to monthwise. Below is attached example of how to obtain the monthly gc_score from the daily gc score:

Agent	Daily gc_score	No of audits	No of Passed	No of Failed
A1	75	20	15	5
A1	85	20	17	3

Now I want to convert my above query code in such a way that it takes the average of daily gc_score column and give a monthly gc_score per agent. For instance, the monthly gc_score will be obtained by: (75+85)/2 = 80 And the final possible outcome will be:

Agent	Monthly gc_score
A1	80

I am wondering how can I obtain this? I have tried with following code:

$query = DB::table('qc_feedbacks')
    ->selectRaw("
        COUNT(cru_agent_detail_id) as numberofEvaluations,
        COUNT(CASE WHEN qc_feedbacks.status='Passed' THEN 1 ELSE Null END) AS passed,
        COUNT(CASE WHEN qc_feedbacks.status!='Passed' THEN 1 ELSE Null END) AS failed,
        AVG(score) AS gc_score,
        cru_agent_details.cru_id AS cru_id,
        cru_agent_details.name AS agentName,
        cru_agent_details.team_lead AS team_lead,
        cru_agent_detail_id,
        DATE_FORMAT(audit_date, '%Y-%m') AS month
    ")
    ->join('cru_agent_details', 'cru_agent_details.id', '=', 'qc_feedbacks.cru_agent_detail_id')
    ->groupBy('month', 'cru_agent_detail_id')
    ->orderBy('month', 'desc');

Answer 1

From the first query, you can make it as a sub query and then select the avg(gc_score) of it.

Otherwise, you could group by month date:

->groupByRaw('MONTH(audit_date)');