Enhance Views to support proper GROUP BY queries

This might be the same issue as posted here: #381890: Does the views have facility of sum up in serialize way.

Try using the views_calc module and see if that works.

Yes, you're right - views_calc calculates on columns, now a row. If you'd like a field which counts the value of other fields within a particular node, take a look at the "computed field" module - you can use it to add up other values entered into that node.

> That means that every time a department is viewed for any purpose, this figure would have to recalculate.
Actually, only when the page is edited will the field recalculate. Drupal will cache your department node once it's saved, so that even if it's viewed 1000 times after that, your computed field won't have to run again (assuming that you set it to save its result to the database).

You do have a point about time periods, though - that might be useful. Although why don't you just get a view to return all employees from a department, filter by pay period, then use views_calc to total up those amounts?

Although...if your view returns a display by month in a table view, you would need to have a sub-total at the end of each row to show the amount an employee has earned during that time?

Something like:
Jan | Feb | Mar | Total
100 | 200 | 150 | 450

Maybe there's a way to extend views_calc to get it to calculate by row, rather than by column?

That's excellent - good luck!

Here's a post I made a while back that is at least somewhat related to your ideas, if you're looking for some use-cases. In short, it would be useful if it was possible to do filtering on the aggregate results (e.g. only return rows where sum(x) > [some other number]).

http://drupal.org/node/337377

subscribing - I'd like to see this too

okay! Sorry for the incredibly long delay here, I'm buried in this darn queue.

In order for Views to properly support group by, here is what needs to happen:

The support for aggregate function in $query->add_field() needs to be expanded. Right now you can tag whether or not it is an aggregate, but really we need to be able to just pass that in.

The use of 'group by' needs to be a basic field on the view, probably similar to view type. It's one of those fundamental decisions that probably can't be changed when a View is created.

Every handler/argument/filter/field/sort needs to have a flag on it to state whether or not it supports aggregate functions. If it does, then the options screen for that field needs to contain a select widget which contains "Group by this field" and a list of aggregate functions (count, sum, min, max...are there others?)

If a field does not support aggregate functions, then that select must contain only 'group by this field' and it should act appropriately if the group by setting is on in the view.

Every handler that supports group by must then be modified to take into account this aggregate when adding itself to the query. This is going to be the tedious part because there are 130 or so handlers in Views and probably most of them will need to be examined.

We'll have to up the API version of course. Should be fun stuff!

Making this the master issue for dealing with group by, and changing the title appropriately.

Some of this is in Views Calc now, the Views Calc table has options for the aggregation you want to use for each column, so you can select that you want to 'SUM', 'COUNT', etc for each field in the table. I did it not by altering the handlers themselves but by letting you identify in the view which fields need to be aggregated. Views Calc creates a regular view for the main table and then adds an embedded view for each subtotal. I had been planning to add another option let you choose to hide the main table and show the aggregated values only.

Views Calc has another plugin to create groupby totals for the Charts module, That takes a different approach where you identify the field to aggregate on and the field to do the computation on (they are not necessarily the same, you could group by User ID and find the MIN created date).

The aggregated value then also needs some formatting information -- COUNT is always an integer value but SUM needs to pass through a theme to look the same as the individual values within it so has the right number of decimals or the needed prefixes or suffixes.

I also found it was nice to let people right, left, or center justify table columns, because numeric totals look very odd left-justified.

Just some food for thought. Maybe some of the work in Views Calc could be helpful here.

Also Views Calc has a 'Computed field' -- you can create as many custom fields as you need built out of SQL, like 'CONCAT('field_first_name', ' ', 'field_last_name')' or whatever. Then you can add those fields to your view and aggregate by them as well.

Indeed it seems like a lot of it may be necessary. We should get the author of the views_groupby module to weigh in as well.

There are lots of different ways this could be implemented, it will take some thought to figure out the most flexible interface. There will be times when you want to see the details with summary totals after, there will be times you want to see only summary totals. I've even wondered if each group by query should be an attachment to the main view instead of trying to get them into the same view (although then it's hard to style the whole thing into a single table).

Adding new, additional fields to the summary line would add a lot of complexity, I'm not sure it's worth trying to get incorporated. We already have a system for selecting fields for the view, the easiest thing is for those to also be the fields used in the summary.

As for 'Summary views', create a view with arguments and choose the option on the arguments to display the results in a summary and look at the query this creates and the way it is displayed. That's the current 'group by' behavior.

Here's another thing to consider: http://www.oreillynet.com/pub/a/mysql/2007/03/29/emulating-analytic-aka-...

Unfortunately, MySQL doesn't support Analytic functions (yet?), but they are very powerful and that article might have some ideas that prove useful in extending Views.

And yes, I've written many SQL statement generator over the years, so I've got a pretty good idea of how quickly thing can get pretty complex. Unfortunately my skills and patience with PHP are pretty low at the moment. Maybe after I've had a crack at some of the simpler module ideas I'm thinking about I might be ready to lend a hand here. For now the best I can do is lob unsolicited ideas from the sidelines.

Colin

following this issue. Even for basic stuff like tell me how many people voted on option1 in this poll, can't be done yet :( Going to investigate the other modules, but have a feeling that there is no magik bullet.

I think views calc can answer the 'how many people' question.

I achieved "similar" functionality using http://drupal.org/project/views_groupby
Currently it don't support SUM() but adding it is easy.

So, of the MySQL Aggregate functions and PostgreSQL Aggregate functions, which ones would we support?

-Corey

adding a tag and pumping to 3.x

I've had a go at making a patch that focuses more on the aggregation and not the grouping thus far -- I've attached a patch that will let you do (hopefully) many aggregating functions, such as COUNT(), SUM(), AVG(), MAX() and MIN(). It's quite simple, and thus almost certainly buggy, but I'd be interested to see what people thought. It applies to 6.x-2.x (the latest release), since the CVS version I checked out had errors (possibly/probably my bad).

To use:

1. Add a field to a query.
2. Check the 'aggregate this field' checkbox.
3. Pick an aggregation function.

Views will automatically group any non-aggregated fields (inherited from their support for count queries). I've yet to add more controls for grouping. Maybe it's complete crap, maybe it's a start if someone can re-roll / let me know where I should be looking in CVS.

lunaris

Subscribing.

Subscribing

Subscribing

@lunaris

Applied your patch in #24 -- unfortunately this method seems to break down when relationships are involved. The problem seems to be that when aggregating on a field in a related table, the foreign keys are included in the 'group by' clause which basically prevents any aggregation from taking place.

This definitely reemphasizes the need for some level of control over the fields included in the 'group by' clause.

I've rerolled lunaris's patch in #24 with the following additions:

1) You can now flag fields for grouping under the field options dialog
2) Added a 'Concatenate' aggregation type (MySQL) that wraps the field in GROUP_CONCAT() and out puts a comma delimited value list

When one or more fields are flagged for grouping, views default 'group by' functionality is overridden (i.e. the only fields included in the group by clause are those that have been explicitly flagged within the view). These changes are all MySQL specific, and take advantage of the fact that MySQL does not require all non-aggregate fields to be included in the group by clause.

@ISPTraderChris: I think that you should use a previous version of views, or maybe views 2, because this patch doesn't apply.

I have rerolled this patch to views 3.x and make some testing. It seem to be working. Even with relathionships.

It's important test it with PostgreSQL i'm only using MySql.

Also I have removed GROUP_CONCAT() because it isn't compatible with PostgreSQL, however there is a way to implement it. More information here

Changing status.

@dagmar - thank you. The patch was against the current version of views2 - I will have to go back and see where my patch creation went wrong.

I reviewed your link on PostgreSQL concatenation and it seems straight forward -- basically the two concatenation functions would be:

1) For MySQL: GROUP_CONCAT(field SEPARATOR delimiter)
2) For PostgreSQL: ARRAY_TO_STRING(ARRAY_AGG(field), delimiter)

This should be straight forward to implement, but I am not sure how to check whether MySQL or PostgreSQL is being used?

Patches for new features have to be designed for 3.x version. My last patch is a rellol of yours for views 3.x.

but I am not sure how to check whether MySQL or PostgreSQL is being used?

You can use:

 if ($GLOBALS['db_type'] != 'pgsql') {
    //...
 }
 else {
    //...
 }

Also we need a bit of documentation for this patch. My english is not good. Who can write it?

I will volunteer to provide the documentation. I am still new to the drupal contribution process -- can you point me to a few examples of the documentation type and format required? Also how shall I submit the documentation?

Sub

This is such a needed enhancement for Views. Can't wait. In fact I didn't. Basic features in patch in 29 worked great in views 2 (mysql)...I actually didn't realize this was for 2. Finally some good reporting abilities with views! You all rock.

Yeah, patch #29 does EXACTLY what I needed it to...Thanks.

sub

Is there any chance of retrofitting this to views 2? I'll test some scenarios with patch 29 if that helps.

nice one! patch 29 worked fine. i just use it to group by the node.nid in order to have just one row for each node after some joins.

+++ views/handlers/views_handler_field.inc	2009-09-01 12:20:15.109375000 -0400
@@ -48,6 +48,17 @@ class views_handler_field extends views_
+   * aggregation functions (such as COUNT, SUM, MAX, etc.) to be
+++ views/includes/query.inc	2009-09-01 12:23:51.312500000 -0400
@@ -928,6 +931,12 @@ class views_query {
+        $groupby_fields[] = $fieldname;

I had to change this to $groupby_fields[] = $string; to get it to work with a CCK field. MySQL at least doesn't like to have aliases in group bys, just as in Wheres.

This review is for #29 bot also applies to #30.

I'm on crack. Are you, too?

Here's a little bit changed patch (against views 2.x) that abstracts the available aggregation functions a bit better.

Subscribing and hoping to be able to help with some testing.

Subscribing

subscribing - interesting feature I would like to follow the development of.

I ran into a problem while trying to rework this that required rethinking it pretty seriously.

The problem is this: I wanted to do more than display aggregations. We need to be able to sort and filter on them.

In doing this, I discovered that if you change node.title into COUNT(node.title) the operations on node.title are no longer relevant. "contains" and "starts with" don't really work when what you really want to do is work on numbers.

Likewise, in fields 'link this field to its node' isn't relevant when you have 3 rather than 'E Pluribus Unum'. And you don't need to go to great lengths to add additional fields needed to render something when you're just counting how many instances are in the database.

What I ended up deciding was that the group by system needs to be able to introduce alternate handlers that know what the new data is going to look like.

In order for all this to work right, I bumped up the Views API version on this. That means no modules will put their stuff in until they're retooled for this. All they really need to do, for this patch, is to correctly mark themselves as being aggregatable or not. However I suspect there will be a few more wrinkles when we get to CCK. I hope to take a crack at that tomorrow.

Right now, fields mark themselves as not being valid with group by queries by adding 'no group by' into their definition. I did this with taxonomy: all terms as a test, but we need to go through (sigh) every field and determine if it should be excluded with group by queries.

I've no idea how this patch will react to a wide assortment of Views, but there's still a bit more work to do anyway:

Here's a screenshot:

Subscribing - This will be amazing

Here's an updated patch for Views and a patch for CCK to utilize the new feature.

I imagine there's a whole lot of edge cases that will have to be filled in as we encounter them.

hmm... subscribing as it seems related to #616370: node.type is added to SELECT after being added as a filter

In regards to the patch for CCK... I haven't tested, just browsed the patch, so I may be missing something, but it seems to me if we do not pass a pseudo node to content_format(), then we'll break modules that take advantage of the $node object passed to hook_field_access(). Please, see #514452: Add new argument $node to content_access() to enhance the context for hook_field_access()

We may also break other things if we call content_format() without a pseudo node without the type (maybe implementations of field formatters, hook_field('sanitize'), ...).

Markus: Note that when using group by, the idea of a node may be gone.

Imagine grouping on a taxonomy-like field. Lots of nodes may have the text field "Cool"; when you render it, there isn't a single node. Yes, you will lose the distinctiveness of nodes, but that is the point of grouping, and is a necessity.

I will probably commit this soon so I would love some comments on the patch.

+++ plugins/views_plugin_display.inc	4 Nov 2009 19:32:16 -0000
@@ -946,6 +981,15 @@ class views_plugin_display extends views
+        $form['group_by'] = array(
+          '#type' => 'checkbox',
+          '#title' => t('Group by'),
+          '#description' => t('If enabled, some fields may become unavailable. All fields that are selected for grouping will be collapsed to one record per distinct value. Other fields which are selected for aggregation will have the function run on them. For example, you can group nodes on title and count the number of nids in order to get a list of duplicate titles.'),
+          '#default_value' => $this->get_option('distinct'),
+        );

It should be:

'#default_value' => $this->get_option('group_by'),

I have installed this patch in views 3. I make tests for Node type and Node title, and also an integer cck field.

There is an sql error when users select a group by sort different than defined in fields.

Steps to replicate:

Fields:
Node: Type
COUNT(Node: Title)

Order:
COUNT(Node: Type) asc

This settings produce this SQL:

SELECT COUNT(node.type) AS node_type,
COUNT(node.title) AS node_title
FROM node node 
GROUP BY 
ORDER BY node_type ASC

And this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY

However order by COUNT(node.type) works fine.

I can reproduce the error if i have distinct activated

$view = new view;
$view->name = 'blub';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'type' => array(
    'group_type' => 'group',
    'label' => 'Type',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'link_class' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
      'help' => '',
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'link_to_node' => 0,
    'exclude' => 0,
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
  'title' => array(
    'group_type' => 'count',
    'label' => 'Title',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'link_class' => '',
      'prefix' => '',
      'suffix' => '',
      'target' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'strip_tags' => 0,
      'html' => 0,
      'help' => '',
    ),
    'empty' => '',
    'hide_empty' => 0,
    'empty_zero' => 0,
    'link_to_node' => 0,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'title' => array(
    'group_type' => 'group',
    'order' => 'DESC',
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('cache', array(
  'type' => 'none',
));
$handler->override_option('distinct', 1);
$handler->override_option('group_by', 1);

Subscribing by positing a patch which fixes the first issue :)

Here is a patch which fixes the sql error with distinct, but i'm not really sure, whether this is the right method:

      elseif ($this->distinct && !in_array($fieldname, $this->groupby)) {
        $string = $GLOBALS['db_type'] == 'pgsql' ? "FIRST($string)" : $string;
+        $non_aggregates[] = $fieldname;
      }

The DISTINCT problem was a little deeper than that, and existed because I had stopped putting the base field in automatically. When a query is set distinct, though, the base field still needs to appear. An add_field fixed that, except that we also need to re-order DISTINCT fields to the beginning, as mysql doesn't like them not being at the beginning.

I also added some uniqueness checking on fields, which could have some wide-ranging effects. I then committed the patch because maintaining this is unwieldy. Views 3 is now less stable, probably, but has group by. Because of htis, I tagged an alpha1 prior to the commit so people can have a solid point for actually using Views 3.

Here's the patch for the purposes of porting to 7.x

Mh. I think i have to invest some time to figure this out

patch -p0 < group-by.patch
patch unexpectedly ends in middle of line
patch: **** Only garbage was found in the patch input.

Is it transparent to CCK fields, or do we need to do anything to CCK field handlers in order to keep compatibility with both Views 2 and 3? (I'm not using Views 3, so it's not easy for me to see)

Related issue in CCK queue: #616370: node.type is added to SELECT after being added as a filter

markus: the patch I posted for CCK will need to be applied to CCK. It should work against both Views 2 and Views 3, though moving forward that may become a little cumbersome, but I will do my best to try not to make your life too hard keeping up with this.

It does need to be tested against Views 2 to make sure it breaks nothing.

Ok. i took a manuall diff between two versions.

Here is the first version BUT! i didn't converted yet the hard part, the sql generation, because the 7.x version uses dbtng

#649036: Support for Views 2 & 3

Hi, I use the path from #29 and it works fine for my views-version. But I now have to upgrade to views 6.x-2.8, because of some bug fixes.
I can't apply the path against this version - it does not work.

Can I use one of the other patches to get the grouping and aggregation functions in the latest stable views?
Or is there an "official" version allready containing this functionality (dev, or views 3??).

Kind regards

Sorry, but Group By support will not be included into views 2.x.

You can download the development version of views 3 following this instructions

http://drupal.org/node/38878/cvs-instructions/DRUPAL-6--3

Views 3 already implement this feature and it is working pretty well.

However views 3 is not ready for production sites, and it has not been tested a lot, probably you refer to use the alpha-2 of views 3 when it is ready (alpha-1 is very unestable)

There is a list of issues to do before Views 3 - alpha 2 is ready:

http://drupal.org/project/issues/search/views?issue_tags=alpha-2%20blocker

Hi dagmar,
thank you for the reply. But, is it possible to modify the patch from #29 to work with the currente stable of views2? Or is this allready done in one of the following patches?

@vistree, sorry, but the views 3 patch is not the same that you are using in views 2.

The committed patch to views 3 was #59 with a size of 50kb, you are using a patch much more simpler instead (only 6kb).

I will not backport #29 to views 2 because this is almost a hack. So, if you want apply the patch by yourself, only add the lines with a + to the correct file, and delete the marked with a -.

Please, don't change the version of this issue again. We know that this is a feature really useful, but you have to understand that our purpose is provide a stable code, and backport it to all version of views is not always possible.

Recommend that the Views 2.x version of the charting code be merged with the views_groupby module..

Backporting to views2 would be a major pane. Just my 0.02$

But i have a reason why i post it, group-by works now.

The patch does not only include this patch it adds
- compile_fields
- fixes for groupby
- more simpletests for groupby
TODO: find the issue links to this.

PS: tests are awesome to get things working. This really safed my life!!

the count query does not get any fields, thats critical.

newest version

new version:
merged newest changes to d7

The current progress is on http://github.com/damz/views-7/tree/public-group-by

This does now works.

Here is a current patch

So next version.

This fixes argument-summaries. Tests are running fine now. Manual testing too.

Pluggable pagers: 28 passes, 27 fails, and 1 exception

They are currently broken, because the pluggable pagers aren't commited yet to d7, only the tests.

After some review of dagmar in irc. It was commited-

Applying the CCK patch posted http://drupal.org/node/695298 to CCK 6.x-2.8 doesn't work. I am running against 6.x-3.x-dev built on 2010-Sep-14 Below is a view definition that generates the extra column

$view = new view;
$view->name = 'avg_age_by_state';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = '3.0-alpha1';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Field: Content: State (field_state) */
$handler->display->display_options['fields']['field_state_value']['id'] = 'field_state_value';
$handler->display->display_options['fields']['field_state_value']['table'] = 'node_data_field_state';
$handler->display->display_options['fields']['field_state_value']['field'] = 'field_state_value';
$handler->display->display_options['fields']['field_state_value']['label'] = 'State';
$handler->display->display_options['fields']['field_state_value']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_state_value']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_state_value']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['field_state_value']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_state_value']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_state_value']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_state_value']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_state_value']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_state_value']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_state_value']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_state_value']['link_to_node'] = 0;
$handler->display->display_options['fields']['field_state_value']['multiple']['group'] = FALSE;
/* Field: Content: Age (field_age) */
$handler->display->display_options['fields']['field_age_value']['id'] = 'field_age_value';
$handler->display->display_options['fields']['field_age_value']['table'] = 'node_data_field_age';
$handler->display->display_options['fields']['field_age_value']['field'] = 'field_age_value';
$handler->display->display_options['fields']['field_age_value']['group_type'] = 'avg';
$handler->display->display_options['fields']['field_age_value']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_age_value']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_age_value']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['field_age_value']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_age_value']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_age_value']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_age_value']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_age_value']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_age_value']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_age_value']['empty_zero'] = 0;
/* Filter: Node: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'person' => 'person',
);

Generates the sql

SELECT node_data_field_state.field_state_value AS node_data_field_state_field_state_value, AVG(node_data_field_age.field_age_value) AS node_data_field_age_field_age_value, node_data_field_age.field_age_value AS node_data_field_age_field_age_value_1 FROM node node  LEFT JOIN content_field_state node_data_field_state ON node.vid = node_data_field_state.vid LEFT JOIN content_type_person node_data_field_age ON node.vid = node_data_field_age.vid WHERE node.type in ('person') GROUP BY node_data_field_state_field_state_value, node_data_field_age_field_age_value_1

And there is the content type definition

$content['type']  = array (
  'name' => 'Person',
  'type' => 'person',
  'description' => '',
  'title_label' => 'Name',
  'body_label' => 'Bio',
  'min_word_count' => '0',
  'help' => '',
  'node_options' => 
  array (
    'status' => true,
    'promote' => true,
    'sticky' => false,
    'revision' => false,
  ),
  'old_type' => 'person',
  'orig_type' => '',
  'module' => 'node',
  'custom' => '1',
  'modified' => '1',
  'locked' => '0',
  'comment' => '2',
  'comment_default_mode' => '4',
  'comment_default_order' => '1',
  'comment_default_per_page' => '50',
  'comment_controls' => '3',
  'comment_anonymous' => 0,
  'comment_subject_field' => '1',
  'comment_preview' => '1',
  'comment_form_location' => '0',
);
$content['fields']  = array (
  0 => 
  array (
    'label' => 'Age',
    'field_name' => 'field_age',
    'type' => 'number_integer',
    'widget_type' => 'number',
    'change' => 'Change basic information',
    'weight' => '-4',
    'description' => '',
    'default_value' => 
    array (
      0 => 
      array (
        'value' => '',
        '_error_element' => 'default_value_widget][field_age][0][value',
      ),
    ),
    'default_value_php' => '',
    'default_value_widget' => NULL,
    'group' => false,
    'required' => 0,
    'multiple' => '0',
    'min' => '0',
    'max' => '',
    'prefix' => '',
    'suffix' => '',
    'allowed_values' => '',
    'allowed_values_php' => '',
    'op' => 'Save field settings',
    'module' => 'number',
    'widget_module' => 'number',
    'columns' => 
    array (
      'value' => 
      array (
        'type' => 'int',
        'not null' => false,
        'sortable' => true,
      ),
    ),
    'display_settings' => 
    array (
      'label' => 
      array (
        'format' => 'above',
        'exclude' => 0,
      ),
      'teaser' => 
      array (
        'format' => 'default',
        'exclude' => 0,
      ),
      'full' => 
      array (
        'format' => 'default',
        'exclude' => 0,
      ),
      4 => 
      array (
        'format' => 'default',
        'exclude' => 0,
      ),
      2 => 
      array (
        'format' => 'default',
        'exclude' => 0,
      ),
      3 => 
      array (
        'format' => 'default',
        'exclude' => 0,
      ),
    ),
  ),
  1 => 
  array (
    'label' => 'State',
    'field_name' => 'field_state',
    'type' => 'text',
    'widget_type' => 'text_textfield',
    'change' => 'Change basic information',
    'weight' => '-3',
    'rows' => 5,
    'size' => '2',
    'description' => 'State code',
    'default_value' => 
    array (
      0 => 
      array (
        'value' => '',
        '_error_element' => 'default_value_widget][field_state][0][value',
      ),
    ),
    'default_value_php' => '',
    'default_value_widget' => 
    array (
      'field_state' => 
      array (
        0 => 
        array (
          'value' => '',
          '_error_element' => 'default_value_widget][field_state][0][value',
        ),
      ),
    ),
    'group' => false,
    'required' => 0,
    'multiple' => '0',
    'text_processing' => '0',
    'max_length' => '',
    'allowed_values' => '',
    'allowed_values_php' => '',
    'op' => 'Save field settings',
    'module' => 'text',
    'widget_module' => 'text',
    'columns' => 
    array (
      'value' => 
      array (
        'type' => 'text',
        'size' => 'big',
        'not null' => false,
        'sortable' => true,
        'views' => true,
      ),
    ),
    'display_settings' => 
    array (
      'label' => 
      array (
        'format' => 'above',
        'exclude' => 0,
      ),
      'teaser' => 
      array (
        'format' => 'default',
        'exclude' => 0,
      ),
      'full' => 
      array (
        'format' => 'default',
        'exclude' => 0,
      ),
      4 => 
      array (
        'format' => 'default',
        'exclude' => 0,
      ),
      2 => 
      array (
        'format' => 'default',
        'exclude' => 0,
      ),
      3 => 
      array (
        'format' => 'default',
        'exclude' => 0,
      ),
    ),
  ),
);
$content['extra']  = array (
  'title' => '-5',
  'body_field' => '-1',
  'revision_information' => '2',
  'author' => '1',
  'options' => '3',
  'comment_settings' => '4',
  'menu' => '-2',
  'locations' => '0',
);

In my opinion this issue should remain closed and work in the #695298: Allow CCK to work with GROUP BY support in Views, or at least open new follow ups.