first of all I simplified my query so it might be more useable to other users on this site.
I'm looking for a solution to use group by on multiple fields.
This query selects the values of the pop_links module where only unique values can remain. resulting in 1 value per day, node and hostname. ( to display only one click per hostname a day )
Here's my plain sql query:
SELECT cid, nid, from_unixtime(timestamp) FROM pop_links_stats p
GROUP BY date(from_unixtime(timestamp)), hostname, nid ORDER BY timestamp
Here is a simplified version of the drupal 7 query I'm currently using:
$query = db_select('pop_links_stats','p');
$query->fields('p', array('cid','nid','timestamp'));
$results = $query->execute()->fetchAll();
I'm also already using the join method of the db api but I don't know how to add the above conditions.
Adding this below will group the fields by every day but I want to groupby on 3 fields.
$query->groupBy('DATE(FROM_UNIXTIME(p.timestamp))');