Get Values with AVG(value) and groupBy(WEEK(FROM_UNIXTIME(date)))
Hello,
i'm trying to build this query in an Model:
SELECT date, AVG(value) as value FROM card_value WHERE cardid = " . $cardid . " GROUP BY WEEK(FROM_UNIXTIME(`date`)) ORDER BY date ASC
The Model looks like the following at the moment:
public function ValuesToCard() {
return $this->hasMany('App\Models\TCG\CardValue', 'cardid')->select('date', DB::raw('AVG(value) as value'))->groupBy('WEEK(FROM_UNIXTIME(date))')->orderBy('date', 'asc');
}
The error shown is:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'WEEK(FROM_UNIXTIME(date))' in 'group statement' (SQL: select `date`, AVG(value) as value from `card_value` where `card_value`.`cardid` in (7987) group by `WEEK(FROM_UNIXTIME(date))` order by `date` asc)
public function ValuesToCard() {
return $this->hasMany('App\Models\TCG\CardValue', 'cardid')->select('date', DB::raw('AVG(value) as value'))->groupBy(DB::raw('WEEK(FROM_UNIXTIME(date))'))->orderBy('date', 'asc');
}
Now the error is the following:
SQLSTATE[42000]: Syntax error or access violation: 1055 'd0170cbc.card_value.date' isn't in GROUP BY (SQL: select `date`, AVG(value) as value from `card_value` where `card_value`.`cardid` in (7987) group by WEEK(FROM_UNIXTIME(date)) order by `date` asc)
