I'm trying to get max, average, and latest values from my table of stuff. Two columns exist that are pertinant: created_at and value. I'm interested in grouping by day, but needing the latest value prevents me from being able to use group by. So I'm using window functions and I have the SQL that works from the MySql client:
select distinct
DAYOFWEEK(created_at) as dow,
MAX(value) OVER(PARTITION BY dow ORDER BY created_at) as maxv,
AVG(value) OVER(PARTITION BY dow ORDER BY created_at) as avgv,
LAST_VALUE(value) OVER(PARTITION BY dow ORDER BY created_at) as lastv
FROM `insight_follower_counts`;
However, when I try to run this through PHP PDO (actually I'm using Laravel's interface)
$data = DB::select('select distinct ' .
'DAYOFWEEK(created_at) as dow, ' .
'MAX(value) OVER(PARTITION BY dow ORDER BY created_at) as maxv, ' .
'AVG(value) OVER(PARTITION BY dow ORDER BY created_at) as avgv, '.
'LAST_VALUE(value) OVER(PARTITION BY dow ORDER BY created_at) as lastv ' .
'FROM insight_online_followers');
I get the following error:
Doctrine/DBAL/Driver/PDOException with message 'SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause'
Is there a way to do this through PHP PDO? I tried searching but can find nothing related to window functions used via PHP.
