Group by Using Avg dosn't work

Using aggregate functions with Field API fields is not possible at the moment, it's still broken.

Can't remember one at the top of my head, so let's keep it here for now.

subscribe

I created a duplicate of this ticket, here is my use case for more information.

When I use group by in views with a field from fieldapi views appears to be including too many of the columns from the fieldapi table in the query. A description of the view I tested this with is below, along with the code from the view.

I am grouping by node type, then doing a count of nid and an average of a fieldapi field named field_small that is just an integer. If I get rid of field_small the grouping works perfect. When field_small is included the group by statement includes language, delta, bundle, entity type, and even value (which should not be included). In the select statement it is averaging the entity id, instead of averaging the value. The query is below, followed by what I would think the query should be.

------------current query
SELECT
node.type AS node_type,
field_data_field_small.entity_type AS field_data_field_small_entity_type,
field_data_field_small.delta AS field_data_field_small_delta,
field_data_field_small.language AS field_data_field_small_language,
field_data_field_small.bundle AS field_data_field_small_bundle,
field_data_field_small.field_small_value AS field_data_field_small_field_small_value,
COUNT(node.nid) AS nid,
AVG(field_data_field_small.entity_id) AS field_data_field_small_entity_id

FROM
{node} node
LEFT JOIN {field_data_field_small} field_data_field_small ON node.nid = field_data_field_small.entity_id AND (field_data_field_small.entity_type = :views_join_condition_0 AND field_data_field_small.deleted = :views_join_condition_1)

GROUP BY
node_type,
field_data_field_small_entity_type,
field_data_field_small_delta,
field_data_field_small_language,
field_data_field_small_bundle,
field_data_field_small_field_small_value

LIMIT 10 OFFSET 0

-----------what query should be

SELECT
node.type AS node_type,
COUNT(node.nid) AS nid,
AVG(field_data_field_small.field_small_value) AS field_data_field_small_field_small_value

FROM
{node} node
LEFT JOIN {field_data_field_small} field_data_field_small ON node.nid = field_data_field_small.entity_id AND (field_data_field_small.entity_type = :views_join_condition_0 AND field_data_field_small.deleted = :views_join_condition_1)

GROUP BY
node_type

LIMIT 10 OFFSET 0

Thanks,

Kevin

I can confirm this issue on field type 'Integer'.

Subbed

I made an attempt at getting group by support working for field api, but frankly I think we have a ways to go. Still, have a minimal configuration actually working.

@merlinofchaos Thanks! Very excited about this feature, I have very little dev experience but have a bit of SQL knowledge and would be happy to do some testing when the time comes.

I'm going to mark this fixed; the basic feature works. The rest of the way to go is in this issue right now: http://drupal.org/node/1073350

To make it work I needed to remove the "sort criteria" from the particular view where I wanted grouping. Just a note if other people are trying. Thanks.