By using hook_views_query_substitutions in the views_grouby.module I was able to rewrite finished SQL statement. The first thing that is rewriten is GROUP BY clause so that only the fields that were selected go after the GROUP BY. In views_groupby_handler_field_grooupfield.ind options for Sum and Avg functions are added. Since those values are stored in the COUNT variable, Views will build a query with COUNT aggregation function. In the above hook I rewrote COUNT (if needed) into SUM or AVG if COUNT variable held the appropriate value.

This solved quite a few of my issues, hope it helps.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

singularo’s picture

Excellent, makes the group by that much more usable!

bakr’s picture

FileSize
107.46 KB

My Evaluation:

I am using:

* view-group-by 6.x-1.0-rc2
* cck 2.6
* views 2.8

Before using the above patch:

- Aggregation worked only for field added from the "Node" group.
- Aggregation DID NOT work once any field was added from the "Content" group.

After patching:

- I just love it, it works on both cases.

But ...

No more I can use views-calc to show the the total of an aggregated column.

I am not sure is it a bug in v-calc or in v-aggregate, or they do not seem to work with each other.

bakr’s picture

FileSize
96.61 KB

Oops, I missed the the following screenshot of the table views calc settings under.

kartelusm’s picture

I haven't been using views-calc module, but I'll get it, look into the problem and hopefully find the solution.

Could you post SQL Aggregation: Group By Fields screen?

kartelusm’s picture

FileSize
240.14 KB
204.2 KB

I tried views_calc out and I believe that the problem is not caused by the patch, but rather by the new version of views. The thing is that "Total count" line is not displayed even when views_groupby module is disabled. It should work with views 2.6. If you try it, please tell me if that solves your issue.
Here are some pictures (without views_groupby)

bakr’s picture

FileSize
102.42 KB

Thanks Nikola,

Please find the attached.

Best Regards

bakr’s picture

By the way,

Both modules are having their own strength area

:views calc
:views group by

But the v-g-b module is having a stronger shine in cross-referenced queries, that is a good muscle, and mind you, this is a critical edge for the drupal platform, to be able to use it in future in enterprise envinronment.

Quote from views-Cals page:

The complex queries created are not relationship-safe yet, so you can't do accurate calculations on fields coming from relationships.

bakr’s picture

Based on above, and rationally enough:

At least, I confirm there is no harm of the above patch. (I really liked it, especially for building charts :-)

I shall be building more complex views reports that shall rely on more relationships, and shall exploit the added (sum,count,average,min,max) functionality.

As such, shall report back my finding with the stability of the above patch, which I hope it shall be eventually committed into the first release.

Regards
Bakr

redben’s picture

Great !
Would be good if we could have COUNT and SUM on the same query

sabroff’s picture

Category: feature » bug

I was having problems with the views_groupby module so I tried the patch by kartelusm.
But it left my GROUP BY without any fields. "GROUP BY ." was all I got.
I am very new to Drupal and almost as new to the forum so I may not be reporting my problems clearly enough.
Before the patch I had 'extra' fields in the group by causing problems with the query.
After the patch, I have none.
We are using views version = "6.x-2.6" and the views_groupby version = "6.x-1.0-rc2".

kartelusm’s picture

I don't think I will be able to look into it before Tuesday (Easter holidays and all), but just to see if I understand you correctly: all the fields after the GROUP BY statement are gone? If that is the case, you could post some screen shots of views page with the form from this module and we'll see if we can solve the problem. One of the features of the patch is to remove the extra fields from GROUP BY, and as far as I know, it works fine.
As for the versions, I wrote the patch using the same versions you mentioned, but tried it since with Views 2.8 and it works.

christiaan_’s picture

Thank you so much for this patch - I was using the latest dev version - and had no joy. Then went back to RC-2 and applied the patch. Now everything is working perfectly. Been looking for a solution for a long time. Thanks again.

cashwilliams’s picture

This is a great patch! Does exactly what I needed and saves me from having to write a custom views handler!

Thanks!

chrisirhc’s picture

I've posted an alternative at #669070: The rest of Aggregate Functions (AVG SUM MIN MAX) that doesn't require as much tweaking. Could anyone test it for me?

Tokoh’s picture

Hi, I know this is an old thread but I have the same problem with views calc and group by as you, Bakr, had in February. The suggestion from kartelusm was to use views 2.6 and the ask to say if this fixed the problem. Did it fix the problem?

florian.cathala’s picture

FileSize
4.61 KB

Hi,

The patch is not PHP 5.3 compliant, here is the warning :

warning: Parameter 1 to views_groupby_views_query_substitutions() expected to be a reference, value given in /var/www/mysite/includes/module.inc on line 483.

Here is the modified patch which won't trigger any warning.

Thank you,

Florian

DevElCuy’s picture

FileSize
3.93 KB

There was a typo in patch at 16 (by @florian.cathala), attached is fixed version.

DevElCuy’s picture

FileSize
3.92 KB

this time following coding standards.

shyam541’s picture

Hi,

Is this patch commited....atleast to the development brach.

Thanks
Shyam

arithmetric’s picture

Title: Rewriteing GROUP BY part of the SQL and cheating to integrate Sum and Avg functions » Rewriting GROUP BY part of the SQL and cheating to integrate Sum and Avg functions
Status: Needs review » Closed (duplicate)

A similar approach to this functionality was developed in #669070: The rest of Aggregate Functions (AVG SUM MIN MAX) and is now committed to the development branch.