Support For add() & sum() MySQL Grouping Features?

subscribing

It depends on Views module itself adding such support.

We will ping them about it soon.

I am also interested in adding more functions, especialy SUM.

thanks

After some research, changed:

--    $options_sql_func = array( 'count' => t('Count'));
++    $options_sql_func = array( 'count' => t('Count'),
++                                    'sum' => t('Sum') );

in "views_groupby_handler_field_groupfields.inc"
and added

      else if (!empty($field['sum'])) {
        $string = "SUM($string)";
        $has_aggregate = TRUE;
      }

in query.inc linke 939 inside views module.

Now sum works, but how do I make the changes into views official release?

sorry... meant to post this as a reply to #4. just attempted that solution, but it didn't work for me...

Where does your problem lies? it didn't appear in views edit form the "sum" option? or it appears but do nothing?

Did you change

      if (!empty($field['count'])) {
        $string = "COUNT($string)";
        $has_aggregate = TRUE;
      }
      else if (!empty($field['aggregate'])) {
        $has_aggregate = TRUE;
      }

to

      if (!empty($field['count'])) {
        $string = "COUNT($string)";
        $has_aggregate = TRUE;
      }
      else if (!empty($field['sum'])) {
        $string = "SUM($string)";
        $has_aggregate = TRUE;
      }
      else if (!empty($field['aggregate'])) {
        $has_aggregate = TRUE;
      }

hi enboig -- thanks much for your reply. i am able to choose "sum" from the edit field, but when i do so my fields no longer aggregate.

i've attached my query.inc file (as .txt).

again -- many thanks!

I have just checked views versions and I think you are using an older version of views; I have
// $Id: query.inc,v 1.42 2009/03/24 23:06:24 merlinofchaos Exp $
and you
// $Id: query.inc,v 1.36 2009/01/07 23:34:32 merlinofchaos Exp $

Check upgrading views and modify again query.inc

enboig, you're my hero -- many thanks for your patient help!

@enboig, you can create a patch and post it here. The community can then review it and module author consider adding it.

Thanks for the suggestions, as this is what we were looking for.

I know nothing about CVS and how to create patches; if anybody sends me a "patch for dummies" or "CVS for dummies" I will make it ASAP; I am the first one interested in making my changes permanent into the module.

I have played a little with tortoise CVS and cvs command line, but I have been unable to find views CVS or Views Group By CVS; can anybody give me a hint? I am a total newbe to CVS

here are the patches; query.patch is for views/includes/query.inc

enboig, thanks, your changes are working for me.

Is anyone having trouble sorting with these patches applied? I'm not entirely sure if its even related, or if its a views problem, but the queries generated seem to be adding an extra field to the ORDER BY and GROUP BY statements. I'm grouping by nid, and the query after I try to sort is grouping and ordering by both nid and the field I'm sorting. If I remove the nid from ORDER BY, and the sortable field from the GROUP BY and run the query manually, it works as expected.

In short, when I sort, I'm getting:

GROUP BY nid, sortable_field ORDER BY nid ASC, sortable_field ASC

should be:

GROUP BY nid ORDER BY sortable_field ASC

Seems like it might be an easy fix, just not sure where to look.

I am having the same error when trying to use AJAX to sort the table. The problem seems to be in function add_orderby() in query.inc:

    // If grouping, all items in the order by must also be in the
    // group by clause. Check $table to ensure that this is not a
    // formula.
    if ($this->groupby && $table) {
      $this->add_groupby($as);
    }

I have a view with to fields (name, sum(value)); I removed:

//  $this->query->add_orderby(NULL, NULL, $orderby_dir, $orderby);

in views_groupby_handler_field_groupfields.inc to allow me to use ajax to sort by 'name'; but the error continues there when trying to order by 'sum(value)' because 'value' gets added to 'group by'.

I think the best way to address this problem would be saving some information (like 'views_groupby_fields_to_aggregate') inside the query (maybe a $query->extra['views_groupby_fields_to_aggregate']) so in 'views_groupby_views_query_alter' it would be possible to remove the aggregated fields from the $query->groupby

Here are the patches to sovle the order problem. I have removed the "sort" option from sql aggregation because now it is useless.
Take into account that the patch also applies to comment13; so you should also patch query.inc

Thanks, works perfectly!

Sorry guys. Need to catch up on these. Looks/sounds great.

Will get back soon.

Are these patches against RC1 or Dev version? Thanks.

d6 dev and RC1 are the same codebase right now.

For what it is worth, patches in #17 did not work for me. I was having the same issue as #14.

Originally I was using RC1 version of views_groupby and patches #13. I found my issue similar to issue #14, was due to having a sort by setup on the view AND the sql aggregation. Once I removed the sort by within the sortby panel, and kept the sortby withing sql aggregation, everything worked as expected.

to solve the "order" problem you need to patch 'views' and 'views group by'; I am talking with 'views' developer to try to find a 'standard' way of tag grouped fields and solve this problem.

Thanks for this patch. I did some basic tests and it appears to be working correctly. Your #17 patch files are not standard patches so I had to apply them by hand. I've attached new versions that do the same thing, but work when using Eclipse.

• query.inc_.patch is for the Views module and applies to /sites/all/modules/views/includes/query.inc
• views_groupby.module_.patch is for the Views_groupby module and applies to /sites/all/modules/views_groupby-DRUPAL-6--1/views_groupby.module
• views_groupby_handler_field_groupfields.inc_.patch is for Views_groupby module and applies to /sites/all/modules/views_groupby-DRUPAL-6--1/handlers/views_groupby_handler_field_groupfields.inc

These patches are for the 6.x-1.x-dev version with the Date of 2009-Apr-02.

I played with the sorting issue being discussed above. If you are using a Views "Sort criteria" on your view, it will mess up the summing. It changes a query from this:

SELECT SUM(node.nid) AS nid,
   node.title AS node_title
 FROM node node 
 WHERE (node.created >= 1230789600) AND (node.created <= 1241067600)
 GROUP BY node_title

To this:

SELECT SUM(node.nid) AS nid,
   node.title AS node_title,
   node.nid AS node_nid
 FROM node node 
 WHERE (node.created >= 1230789600) AND (node.created <= 1241067600)
 GROUP BY node_title, node_nid
  ORDER BY node_nid DESC

Adding "node_nid" and sorting by that messes up the sum because it groups on "node_nid" and cannot sum all of them together as one.
However, removing all Views "Sort criteria" and using "Basic Settings > Style = Table" and setting the table options with a "Default Sort" will work.

So what do I need to do to make query.inc changes into main views module? I have also #481756: saving extra data to $query to manage on hook_views_query_alter to fix some problems between groupby and views; but I have no idea of what to do with my patches.

The above query.inc_.patch should be submitted as a patch to the main Views module. I don't see any reason for them not to include this, but maybe there is a better way to do this in Views.

Since this is a patch to Views, I'm moving this issue over to the Views issue queue, though you should really see @merlinofchaos' comment at http://drupal.org/node/396380#comment-1450732 about allowing Views to support this. It seems like this might be postponed for Views 3.

There is a more complete issue here #396380: Enhance Views to support proper GROUP BY queries. Marking as duplicate.