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

Sinres's avatar

Average from array groupby date

Hello Guys!

I have some array where I must calculate average from date, groupBy date and calculate total average by daily average by count of days.

This is my query:

$turnover = Document::whereBetween('doc_date', ['2021-09-15', '2021-09-17'])
            ->whereIn('doc_type', [1, 4])
            ->select('doc_date', 'shop_id', DB::raw('sum(net_selling_price*quantity) AS sum_turnover'))
            ->groupBy('shop_id', 'doc_date')
            ->get();

return $turnover->groupBy('doc_date');

And this is my returned array:

{
    "2021-09-15": [
        {
            "doc_date": "2021-09-15",
            "shop_id": 6,
            "sum_turnover": "10.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 4,
            "sum_turnover": "20.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 2,
            "sum_turnover": "30.00"
        },
    ],
    "2021-09-16": [
        {
            "doc_date": "2021-09-16",
            "shop_id": 9,
            "sum_turnover": "30.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 2,
            "sum_turnover": "50.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 3,
            "sum_turnover": "40.00"
        },
    ],
    "2021-09-17": [
        {
            "doc_date": "2021-09-17",
            "shop_id": 7,
            "sum_turnover": "60.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 10,
            "sum_turnover": "80.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 1,
            "sum_turnover": "40.00"
        }
    ]
}

How can I get a grouped date array with the average result of these dates based on sum_turnover some like this:

{
    "data": [
        {
            "doc_date": "2021-09-15",
            "daily_average_turnover": "20"
        },
        {
            "doc_date": "2021-09-16",
            "daily_average_turnover": "40"
        },
        {
            "doc_date": "2021-09-17",
            "daily_average_turnover": "60"
        },
]
}

and next this average divided by the number of days some like this:

sum(daily_average_turnover)/$data->count or avg(daily_average_turnover) - (20+40+60/3 = 40 <- desired end result)

I don't know how I can write query I tried as below but in here I must in first calculate sum for every shop and group by shop and date:

$turnover = 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 $turnover->avg('average_turnover') ?? '0.00';

How can I get the desired query? Maybe I should use some foreach or each loop?

Thanks!

0 likes
15 replies
andiliang's avatar
andiliang
4 years ago
Best Answer
Level 39

@sinres i tried in my local db ,


    $products = Product::latest()->get();

    return $products->groupBy('category_id')->map(function($item,$key){
        $data['category_id'] = $key;
        $data['avg_price'] = $item->average('price');
        return $data;
    });

//this is the resule i got 

{
  "12": {
    "category_id": 12,
    "avg_price": 29
  },
  "13": {
    "category_id": 13,
    "avg_price": 18
  },
  "20": {
    "category_id": 20,
    "avg_price": 56.142857142857146
  }
}

I am grouping by category_id , you may replace with date , the colelct keys are the category_id in your case is date.

if want to remmove the keys we can add ->values(); afterward

1 like
Sinres's avatar

@andiliang This is not bad solution but in addition, it needs to calculate the average of your three records. Any ideas? 😁

MichalOravec's avatar

It's not very difficult query.

$data = Document::selectRaw('doc_date, sum(net_selling_price * quantity) / count(id) as daily_average_turnover')
    ->whereBetween('doc_date', ['2021-09-15', '2021-09-17'])
    ->groupBy('doc_date')
    ->get();

return ['data' => $data->toArray()];
2 likes
Sinres's avatar

@MichalOravec Thanks but result is not correct

{
    "data": [
        {
            "doc_date": "2021-09-15",
            "daily_average_turnover": "111.05"
        },
        {
            "doc_date": "2021-09-16",
            "daily_average_turnover": "105.45"
        },
        {
            "doc_date": "2021-09-17",
            "daily_average_turnover": "98.23"
        }
    ]
}

Because I must take count id from each day separately but you take from all days. I want to calculate the average for each individual day and then calculate the average amount based on the number of days

MichalOravec's avatar

@Sinres It doesn't matter.

I didn't add ->whereIn('doc_type', [1, 4]) and shop_id.

Did you just copy my provided code?

Sinres's avatar

@MichalOravec Yes, I calculate based on all stores so I do not use shop_id.

Okey, look this is reality data

{
    "2021-09-15": [
        {
            "doc_date": "2021-09-15",
            "shop_id": 6,
            "sum_turnover": "580.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 4,
            "sum_turnover": "350.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 2,
            "sum_turnover": "290.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 10,
            "sum_turnover": "310.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 3,
            "sum_turnover": "130.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 8,
            "sum_turnover": "240.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 1,
            "sum_turnover": "210.00"
        }
    ],
    "2021-09-16": [
        {
            "doc_date": "2021-09-16",
            "shop_id": 9,
            "sum_turnover": "140.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 2,
            "sum_turnover": "170.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 3,
            "sum_turnover": "340.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 7,
            "sum_turnover": "140.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 4,
            "sum_turnover": "200.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 1,
            "sum_turnover": "110.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 10,
            "sum_turnover": "60.00"
        }
    ],
    "2021-09-17": [
        {
            "doc_date": "2021-09-17",
            "shop_id": 7,
            "sum_turnover": "160.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 10,
            "sum_turnover": "290.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 1,
            "sum_turnover": "200.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 6,
            "sum_turnover": "120.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 9,
            "sum_turnover": "500.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 8,
            "sum_turnover": "70.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 2,
            "sum_turnover": "140.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 3,
            "sum_turnover": "90.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 5,
            "sum_turnover": "100.00"
        }
    ]
}

And now when I used your solution I have calculate like this:

{
    "data": [
        {
            "doc_date": "2021-09-15",
            "daily_average_turnover": "111.05"
        },
        {
            "doc_date": "2021-09-16",
            "daily_average_turnover": "105.45"
        },
        {
            "doc_date": "2021-09-17",
            "daily_average_turnover": "98.23"
        }
    ]
}

But correct solution should return such a result

{
    "data": [
        {
            "doc_date": "2021-09-15",
            "daily_average_turnover": "301.42"  <- 580+350+290+310+130+240+210/7
        },
        {
            "doc_date": "2021-09-16",
            "daily_average_turnover": "165.71"  <- 140+170+340+140+200+110+60/7
        },
        {
            "doc_date": "2021-09-17",
            "daily_average_turnover": "185.55" <- 160+290+200+120+500+70+140+90+100/9
        }
    ]
}

So you see it does matter.

MichalOravec's avatar

@Sinres Which query give you this result?

{
    "data": [
        {
            "doc_date": "2021-09-15",
            "daily_average_turnover": "111.05"
        },
        {
            "doc_date": "2021-09-16",
            "daily_average_turnover": "105.45"
        },
        {
            "doc_date": "2021-09-17",
            "daily_average_turnover": "98.23"
        }
    ]
}
Sinres's avatar

@MichalOravec your query give me this result

$turnover = Document::whereIn('doc_type', [1, 4])
            ->selectRaw('doc_date, sum(net_selling_price * quantity) / count(id) as daily_average_turnover')
            ->whereBetween('doc_date', ['2021-09-15', '2021-09-17'])
            ->groupBy('doc_date')
            ->get();

        return ['data' => $turnover->toArray()];
MichalOravec's avatar

@Sinres And what is the result for this query?

$data = Document::selectRaw('doc_date, shop_id, sum(net_selling_price * quantity) / count(id) as daily_average_turnover')
    ->whereBetween('doc_date', ['2021-09-15', '2021-09-17'])
    ->whereIn('doc_type', [1, 4])
    ->groupBy('doc_date', 'shop_id')
    ->get();

return ['data' => $data->toArray()];
1 like
Sinres's avatar

@MichalOravec I've already tried that :D And look result

{
    "data": [
        {
            "doc_date": "2021-09-15",
            "shop_id": 6,
            "daily_average_turnover": "116.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 4,
            "daily_average_turnover": "116.66"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 2,
            "daily_average_turnover": "96.66"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 10,
            "daily_average_turnover": "103.33"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 3,
            "daily_average_turnover": "130.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 8,
            "daily_average_turnover": "120.00"
        },
        {
            "doc_date": "2021-09-15",
            "shop_id": 1,
            "daily_average_turnover": "105.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 9,
            "daily_average_turnover": "140.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 2,
            "daily_average_turnover": "85.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 3,
            "daily_average_turnover": "113.33"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 7,
            "daily_average_turnover": "140.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 4,
            "daily_average_turnover": "100.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 1,
            "daily_average_turnover": "110.00"
        },
        {
            "doc_date": "2021-09-16",
            "shop_id": 10,
            "daily_average_turnover": "60.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 7,
            "daily_average_turnover": "80.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 10,
            "daily_average_turnover": "96.66"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 1,
            "daily_average_turnover": "100.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 6,
            "daily_average_turnover": "120.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 9,
            "daily_average_turnover": "100.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 8,
            "daily_average_turnover": "70.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 2,
            "daily_average_turnover": "140.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 3,
            "daily_average_turnover": "90.00"
        },
        {
            "doc_date": "2021-09-17",
            "shop_id": 5,
            "daily_average_turnover": "100.00"
        }
    ]
}
Sinres's avatar

@michaloravec it almost worked by this query

$turnover = Document::whereIn('doc_type', [1, 4])
            ->whereBetween('doc_date', ['2021-09-15', '2021-09-17'])
            ->selectRaw('doc_date, sum(net_selling_price * quantity) as daily_average_turnover')
            ->groupBy('doc_date', 'shop_id')
            ->get();

        return $turnover->groupBy('doc_date')->map(function($item) {
            return $item->average('daily_average_turnover');

        });

But how I can take average from this array? When I calculate average I'll get the result I want

{
    "2021-09-15": 301.42,
    "2021-09-16": 165.71,
    "2021-09-17": 185.55
}
Sinres's avatar

I found a solution for me :D Thanks @michaloravec and @andiliang for help me :-)

$days_count = Carbon::parse('2021-09-15')->diffInDays('2021-09-17') + 1;

        $turnover = Document::whereBetween('doc_date', ['2021-09-15', '2021-09-17'])
            ->selectRaw('doc_date, sum(net_selling_price * quantity) as daily_average_turnover')
            ->groupBy('doc_date', 'shop_id')
            ->get();

        $daily_average_turnover = $turnover->groupBy('doc_date')->map(function($item) {
            $data['average_turnover'] = $item->average('daily_average_turnover');
            return $data;
        });

        return $daily_average_turnover->sum('average_turnover') / $days_count;

Please or to participate in this conversation.