Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Sinres's avatar

How to calculate average from every day in array by raw

Hello Guys!

I have some problem. I take some record by doc_date and I want calculate average by every days and next average of this result (sum average from every days divide by count of days).

My some data for better thinks

0 => {#1636
    +"doc_date": "2021-09-15"
    +"shop_id": 2
    +"net_selling_price": "60.0000000"
  }
  1 => {#1674
    +"doc_date": "2021-09-15"
    +"shop_id": 1
    +"net_selling_price": "80.0000000"
  }
  2 => {#1637
    +"doc_date": "2021-09-15"
    +"shop_id": 2
    +"net_selling_price": "30.0000000"
  }
  3 => {#1638
    +"doc_date": "2021-09-16"
    +"shop_id": 2
    +"net_selling_price": "60.0000000"
  }
  4 => {#1635
    +"doc_date": "2021-09-16"
    +"shop_id": 3
    +"net_selling_price": "30.0000000"
  }
  5 => {#1698
    +"doc_date": "2021-09-16"
    +"shop_id": 10
    +"net_selling_price": "40.0000000"
  }
  6 => {#1697
    +"doc_date": "2021-09-17"
    +"shop_id": 10
    +"net_selling_price": "70.0000000"
  }
  7 => {#1723
    +"doc_date": "2021-09-17"
    +"shop_id": 10
    +"net_selling_price": "50.0000000"
  }
  8 => {#1639
    +"doc_date": "2021-09-17"
    +"shop_id": 3
    +"net_selling_price": "80.0000000"
  }

And this is my code

$data = Document::whereBetween('doc_date', ['2021-09-15', '2021-09-17'])
            ->selectRaw('avg(net_selling_price*quantity) AS average_turnover')
            ->groupByRaw('DATE(doc_date)')
            ->get();

return $data->avg('average_turnover')

How I can calculate average from every day and group result by by date and next calculate average by sum selling price / count of number days?

I think my code calculate this result not correct what do you think?

Thanks!

0 likes
9 replies
Sinres's avatar

How I can write this query some like this?

$turnover = Document::whereBetween('doc_date', [$startDate, $endDate])
            ->whereIn('doc_type', self::DOC_TYPE)
            ->selectRaw('sum(net_selling_price*quantity) AS sum_turnover')
            ->groupBy('(shop_id)')
            ->selectRaw('avg(sum_turnover) AS average_turnover')
            ->groupByRaw('DATE(doc_date)')
            ->get();

return $turnover->avg('average_turnover') ?? '0.00';

Now I have error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sum_turnover' in 'field list' (SQL: select sum(net_selling_price*quantity) AS sum_turnover, avg(sum_turnover) AS average_turnover from `documents` where `doc_date` between 2021-09-15 and 2021-09-17 and `doc_type` in (1, 4) and `documents`.`deleted_at` is null group by `(shop_id)`, DATE(doc_date))
MohamedTammam's avatar
$data = Document::whereBetween('doc_date', ['2021-09-15', '2021-09-17'])
            ->selectRaw('avg(net_selling_price*quantity) AS avg_day')
            ->groupByRaw('DATE(doc_date)')
			->get();
$avg = $data->sum('avg_day') / $data->count();
return $avg;
1 like
Sinres's avatar

@MohamedTammam In your first query:

"message": "Call to undefined function Domain\Model\Documents\SUM()",

In second:

"SQLSTATE[HY000]: General error: 1111 Invalid use of group function (SQL: select SUM(avg(net_selling_price*quantity)) / COUNT(*) AS result from `documents` where `doc_date` between 2021-09-15  and `documents`.`deleted_at` is null group by DATE(doc_date))",

:-(

Sinres's avatar

@MohamedTammam I can do this some like this but this not solution for me:

$data = Document::whereBetween('doc_date', ['2021-09-15', '2021-09-17'])
            ->selectRaw('avg(net_selling_price*quantity) AS avg_day')
            ->groupByRaw('DATE(doc_date)')
			->get();
$avg = $data->avg('avg_day';
return $avg;

Look at this image this will best show what query I need

alt text

Sinres's avatar

@MohamedTammam Because in your solution I have only two calculate average but I need sum daily turnover for every shop group by doc_date, shop_id next average of all shops and group by doc_date and last step get average from 3 days.

  1. First step - 'sum(net_selling_price*quantity) AS sum_turnover' groupBy shop_id, doc_date
  2. Second step - 'avg(sum_turnover) AS daily_avg_turnover' groupBy doc_date (result: 15.09 - 100, 16.09 - 80, 17.09 - 90)
  3. Third step - avg(daily_avg_turnover) - (result: 90 -> yellow field in screenshot)

Please or to participate in this conversation.