CODEWITHSUNDEEP
phpmysqllaraveleloquent
Rui Pedro ИИ
Rui Pedro ИИ

Reputation: 21

Sum Max values of a group By

I have this query, that produces next result

$result = DB::table('contagenshora')
            ->select(DB::raw('post_id as post_id'), DB::raw('product_id'), DB::raw('max(val) as maxHour'), DB::raw('hour'))
            ->whereBetween('date', array($dt->toDateTimeString(), $dt2->toDateTimeString()))
            ->where('hour', '=', $actHour->hour)
            ->groupBy(DB::raw('post_id'), DB::raw('product_id'))
            ->get();

Result: Array ( [0] => stdClass Object ( [post_id] => 1 [product_id] => 1 [maxHour] => 4 ) [1] => stdClass Object ( [post_id] => 1 [product_id] => 2 [maxHour] => 3 ) [2] => stdClass Object ( [post_id] => 4 [product_id] => 0 [maxHour] => 6 ) ) )

Now, I need the sum of "maxHour", but grouped by "post_id", I need something like this:

(For example, for post_id = 1, I need the sum of 4+3)

Result: Array ( [0] => stdClass Object ( [post_id] => 1 [maxHour] => 7 ) [1] => stdClass Object ( [post_id] => 4 [maxHour] => 6 ) ) )

How can I do this?

Upvotes: 0

Views: 520

Answers (1)

Ankit Singh
Ankit Singh

Reputation: 922

Just remove the product_id from group by clause and select then. Also no need to use DB::raw() for direct selectable columns.

$result = DB::table('contagenshora')
            ->select('post_id', DB::raw('max(val) as maxHour'))
            ->whereBetween('date', array($dt->toDateTimeString(), $dt2->toDateTimeString()))
            ->where('hour', $actHour->hour)
            ->groupBy('post_id')
            ->get();

Upvotes: 1

Related Questions