Posted by sun on May 1, 2007 at 9:48pm
55 followers
| Project: | Views Calc |
| Version: | 6.x-1.x-dev |
| Component: | Code |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | needs review |
Issue Summary
It would be really nice if Views Calc was also able to group a view by a column and calculate a sum/average/count of another column.
Example:
- Currently, Views Calc is able to calculate and display the sum of comments by user:
user nid comments
KarenS 1 11
KarenS 2 9
sun 1 4
sun 2 8
Sum - 32 - With additional grouping by user and removing the field nid in this view, Views Calc could calculate the sum of all comments in all nodes per user:
user comments
KarenS 20
sun 12
Sum 32
Is this possible with Views Calc?
Comments
#1
subscribing
#2
Since D6 Views has grouping, this is more likely to be implemented there (if at all).
#3
Actually, Views won't do any totaling of the group values, so we still need something else. But hopefully the new grouping in Views 2 will make this easier to do.
#4
I noticed this too. If you select a field in the "Grouping Field" drop down in the "views calc style" settings in views2, the view will be grouped by the field you selected, and the views calc "total" line will show up at the bottom of each "grouping" of nodes, HOWEVER the total will always be the total of all rows! So if you have 5 groupings of a total of 25 nodes, and you are summing a column where the value of all the fields is "1" (thus the total sum is 25) this total will be shown below EACH grouping where one would expect to see a "sub total"
#5
subscribing :-)
#6
subscribing
#7
subscribe. there's another module that does this anyway: http://drupal.org/project/views_groupby
but it's in early developement stage. maybe joining forces ?
#8
#9
subscribing
#10
I've been racking my brain to figure out how to get this functionality only to find out it may not be possible, right now. Any ideas of how to group and total like Sun requested without Views_Calc?
I've got a sports league site I'm trying to finish developing and I was hoping to do a write-up on how it was done (seems to come up every now and then without a decent solution to date).
Any help in getting this implemented would be appreciated.
#11
The views_groupby field works totally differently than views_calc so I don't think they can be combined. Plus it only does counts.
We already have the grand totals and subtotals, so the remaining request would be an option to hide the source data and display only the totals, which should be fairly easy to add.
#12
As to #4, we have both grand totals and sub totals for each page (a recent change to the code).
As to #10, there are no easy alternative ways to do that, that is the purpose of this module. I use Views Calc extensively for things like sports team totals (see http://www.everbloom.us/results for some examples).
#13
Hmm... well there are actually a number of different things noted in this issue. I just added an option to surpress the details and show the totals only and it now shows both subtotals and grand totals. The one thing left is the original issue, which I mis-read or misunderstood, to be able to group by a field and then to calculations on that total. That is not yet implemented.
I'm going to push out a new release with a number of bug fixes. This feature will take more thought so it won't go in and will remain a feature request.
#14
Hi, KarenS!
I try to get sum of money cck fields. And when i try to add to my table field with few taxonomy terms i see few rows with one term in one row. If i use group multiple values option i see sum of all rows without grouping multiple values... Can you fix it?
P.S. In new version i choose only sum and module gives me Total COUNT, Total SUM, Total AVG, Total MIN, Total MAX. Can you fix it?
#15
Please don't dump unrelated issues together, this is about a feature request for grouping. There is another issue about problems with multiple values and the project page says this is a known issue. The last thing is a bug that got introduced in the last commit, but it didn't belong in this issue either.
#16
subscribing...
#17
Subscribe
#18
Subscribe
#19
Subscribe
#20
subscribe. This would be quite useful. If I turn on grouping, I get no calcs at all on certain fields.
#21
subscribe
#22
This isn't the same as displaying all the info on one page at the same time and so probably doesnt work for most of you, but you could create a grouping "taxonomy" for each thing you'd normally group, and and then expose that filter in your view as a selectable list...
#23
subscribe
#24
Subscribe
Also, incase anyone is interested, I solved my need to total a grouped field with a theme override function for my view... it ended up looking like this:
<div class="field-item">$<?php/* Total all the grouped numbers. */
foreach ($field->field_values[$row->nid] as $value) {
$total += $value['value'];
}
print $total;
?></div>
In a template file named views-view-field--field-charge-amount-value.tpl.php.
Hope that helps someone!
Jamie.
#25
I had someone ask me to explain this in a little more detail, so here goes.
I'm using this technique to total a CCK field that allows "Unlimited" values. You can total it in a view created by the Views 2 module, or in the normal node view. I'll just show how to do the Views 2 one. But I highly recommend installing the Advanced Help module. After installed, you can look to the CCK help page for info on how to theme the node view (help/content/theme-field-templates).
The field that I wanted to total is called
field_charge_amount.It shows the active template that is being used in bold.
views-view-field--field-charge-amount-value.tpl.phpto your theme (/sites/default/themes/your_theme/). You just pick the name of the template that fits the amount of specificity you want for the field. Anyway, at this point click on the Field name at the beginning of this block to get the template file contents. Mine looked like this:drupal_set_message("<pre>".print_r($row,1)."</pre>");. You should be able to see just the important variables like this:drupal_set_message("<pre>".print_r($field->field_values[$row->node_vid],1)."</pre>");<?php/* Total all the grouped numbers. */
if (!empty($field->field_values[$row->{$field->field_alias}])) {
foreach ($field->field_values[$row->{$field->field_alias}] as $value) {
$total += $value['value'];
print (!empty($value['value'])) ? '<div class="field-item">$'.$value['value']."</div>\n" : null;
}
if ($total > $value['value']) {
print '<div class="field-item-total">$'.$total."</div>\n";
}
} else {
print '<div class="field-item">none</div>'."\n";
}
?>
Hope that helps someone. Theme overriding can do some amazing, super useful stuff. Totaling a field is just one. :)
Let me know if you have any questions. Thanks! Jamie.
#26
First of all, thanks...
Secondly, I made it all the way to "updating the theme registry and reload the theme info page on the view to see if the bolded theme has changed to yours."
However, it never changed the bolded theme to mine, even after creating a txt file named (in my case) "views-view-field--field-stat-nonpass-td-value.tpl.php," which contained the default php text, and was placed in my "sites/default/modules/views/theme/" directory...
Lastly, From where does one run this command: "drupal_set_message("".print_r($row,1)."");"
Thanks again for all the help, and sorry if I am asking rudimentary stuff...
#27
No problem. The theme template file that you just made would go in /sites/default/themes/your_theme_name/ ... not in the views module's theme folder.
Then to answer your second question, the set message goes into that theme template file that you just put in there... Make sense?
Let me know - good luck!
Jamie.
#28
I made a new post, with a matter related to this.
I want to get a view (with views_calc), with a table that adds a given number field giving separately the subtotal for each group of nodes.
http://drupal.org/node/566714
I desire obtain the sum of values in a given field, separating the subtotal for each content type grouping they belong.
I appreciate the cooperation of the members of the forum.
Sorry for my English
Thanks
#29
Please start a new issue for new questions.
#30
I'm not sure if this is helpful, but perhaps to help prevent reinventing the wheel, it may be worth mentioning that the Charts module (http://drupal.org/project/charts) is able to perform the following operations from views groupings: sum, count, average, minimum and maximum. The user provides the grouping field, the operation, the field to perform the operation and the field to aggregate the results on. Works well with views.
Perhaps the code may be helpful in getting similar functionality into views calc. Just a thought.
#31
Thanks
#32
Finally...
its a views patch, so obviously its not a supported thing, but if you NEED it:
http://drupal.org/node/396380
The fix in #29 did exactly what I needed...
#33
subscribe
#34
Hi JamieR,
I think your solution would be a good workaround. I've done what you've said, but thinking about it how do I get the Sum row with your calculations?
I have a computed field that the value is generated from the CCK content creation page (form) and stored into my database as field_rip_points. I'm displaying them as a column in my View. Summing them is the problem for each user.
How do I create the Sum row? Or is that what the CSS wizardry you have to do? Oh, and do I create a new content type to hold the value of the summed field_rip_points?
Thanks in advance.
Tim T
#35
+1
#36
+1
#37
use latest dev versions of views 3 and ckk 3 to get what you need now
#38
Did what you suggested and found out that views 3 is only for Drupal 7. heart breaking false hope.
#39
I think there is a views 3 for drupal 6 as well. Did you look on the views project page?
http://drupal.org/project/views
#40
Yes, there is a Views v.3 but it is an alfa version: Not recommended for productive sites :-(
#41
I want to know the solution too
#42
Regarding the problem in #4 that was mentioned being resolved in #12, how do I make this work? I am at 6.x-1.3, but still seeing the grand total sum being printed on each sub total sum line.
#43
Same problem as jackfoust. This thread is very hard to follow because of the multiple issues; was there a solution other than the theme function kludge?
#44
This is great, and I have it working... but the code you post doesn't print anything except "none"... I have been able to get it to print the $data of the field, but can't get a total.
thoughts?
ron
#45
+Subscribe
Truyenle
#46
subscribe
#47
Can I get not total, but per group values of views calc with http://drupal.org/project/views_groupby module?
#48
I created a patch for views_calc to get grouping to work. So no more theme-based work-arounds or patching of the views core.
Please note that I also applied this patch: http://drupal.org/node/776848
#49
I am trying to use the patch in #48 but it appears to end unexpectedly in the middle of a line.
#50
Did you apply the patch to the latest dev? I tried it just now with a new checkout of cvs /contributions/modules/views_calc [HEAD] using eclipse. Worked flawlessly.
#51
I did. I might be uneducated about patches and how they work, but when I look at the file linked above it ends in the middle of a foreach statement. Am I missing something or is it normal for a function to end without closure?
#52
I just patched the latest dev version and I am getting the following error and no calculations:
warning: Invalid argument supplied for foreach() in [installation location] on line 188
Any idea what might be going on?
Thanks!
Dave
#53
@peterparker The patch only describes which lines are altered. The ones with + and - signs at the start are the only lines that actually are written to the patched file. In other words: the patch doesn't need to "end with closure" because the closure is in the patched file. And that closure remains as it was before patching.
@steel-track line 188 in which file?
#54
I got the patch to work, thanks for the primer.
Tested it and got an error on line 188 of the theme.inc, presumable just like steel-track.
I took a look and found a $$ which looked out of place (things looking out of place is the limit of my knowhow for the moment). Then again changing that and clearing the caches didn't help.
#55
The data passed to the foreach statement is probably empty. Are you sure you created a views_calc view using at least 1 calculation? And are you sure you've grouped the view?
To avoid the error try wrapping the foreach statement running from line 188 to line 225 into an
<?phpif ($$process)
?>
<?phpif (is_array($$process))
?>
<?php
if ($$process) {
foreach ($$process as $num => $row) {
$type = '';
foreach ($row as $item) {
if (in_array($item, array_keys(_views_calc_calc_options()))) {
$type = $item;
break;
}
}
if (!empty($row->$field_alias) || (isset($row->$field_alias) && $row->$field_alias === 0)) {
// COUNT is always a numeric value, no matter what kind of field it is.
if ($type == 'COUNT') {
$vars[$process][$type][$column] = $row->$field_alias;
}
// Calculations other than COUNT should run the value through the field's theme.
// This will allow dates and numeric values to apply the right formatting to the result.
else {
$vars[$process][$type][$column] = $fields[$field]->theme($row);
}
}
elseif (!empty($type)) {
// Add the calc type label into the first empty column.
// Identify which is the sub total and which the grand total
// when both are provided.
if (empty($added_label[$type])) {
if ($process == 'sub_totals') {
$label = t("Page !Calculation", array("!Calculation" => $type));
}
else {
$label = t("Total !Calculation", array("!Calculation" => $type));
}
$vars[$process][$type][$column] = $label;
$added_label[$type] = TRUE;
}
else {
$vars[$process][$type][$column] = '';
}
}
}
?>
#56
I am in fact using a grouping field and am performing at least one calculation. I also tried performing a count on all of the fields too.
I made the changes recommended in #55 and the errors have gone away, but the view does not display any calculations.
#57
I can maybe take a look at it more myself over the weekend, but it's good to hear I'm not the only one with the issue.
But yeah, I am experiencing the EXACT same issue as peterparker. I have grouped and done a calculation, and yet I am getting that error and no calculations made.
Maybe it's an issue with what we are grouping by? I am grouping by a timestamp that I am changed to have a granularity of month in the field settings. What are you trying to group and calculate peterparker?
#58
I am grouping by date field and am trying to calculate the sum of decimal fields.
#59
I have read that some people have had issues running SUM on decimal fields in Views Calc, so that mightbe a different issue.
However, if you are using a date as well to group, that might be the key to this issue. Maybe there is some sort of issue with gauging granularity of each group?
#60
If you folks could export the problem into a feature, and mail me the feature (or attach it to this thread). Then I can try debugging it.
#61
#62
I can confirm the patch at 48 works with one small fix. See new patch included.
In patching theme.inc:
$totals = $group_totals[$current_group];Should be:
$totals = $view->group_totals[$current_group];Note: I didn't need the patch at #776848: Calculate subtotals for non-node views, e.g., Ubercart orders.
#63
Thanks for the addition askibinski. Patch seems to work as meant.
#64
Thanks everyone for the work on the view calc module. I have been trying to get grouped totals for my app and the patch at #48 and its minor amendment worked, with the problems cited with respect to foreach warning problems if no sums are included.
However as I introduced more data I realised that I need to use the View's pager feature and unfortunately it broke the patch (maybe it also doesn't work with unpatched view calc but I haven't tried this). With the pager feature invoked each group total (say called a TOTAL SUM in bold) is preceded with a new total (say called a Page SUM in normal text) which in fact is the grand total of that column for the application. Furthermore the pager links do not appear at all at the foot of the page and consequently you cannot access pages past the first page.
Although I'm an experienced programmer, I'm still pretty new to Drupal and to PHP. But I need this problem fixed as grouping and totaling are critical to my application. I will spend as much time as it takes to fix this problem, but I'd be interested in hearing from others who might be grappling with/have solved this problem.
I think view calc is a really vital module for Drupal Views and would hate to resort to custom code when others can leverage off this initiative, but I'm interested to hear other views.
#65
I don't really like the grouping solution I posted above. After giving it some thought I reckon the best way to rewrite the grouping support is actually run completely different queries. The way the grouping support works now is actually a loop of the normal views_calc calculations. This results in a couple of empty tables with just a "total" row. What we'd like instead is a single table with the grouped results on each row and a total row at the bottom. It would require a complete rewrite of the grouping patch and probably a partial rewrite of the module. The "grouping" check should be earlier in the process, before the database is queried. And not in the theme.inc, where it currently resides.
#66
Some rework happened in the core of views_calc. It should be some cleaner now.
I'll open a views 3 branch soon. Due to the new features and current dev state i'd expect something like grouping will officially not be supported by views_calc with views 2.
If you like to push this feature, check views 3 capabilities and start helping us port things cleanly to views 3.
#67
Thanks! The patch in 48 with 62's update works for me.
#68
Ah, I wish I had seen this issue...
Here's another patch for Views 2, which adds support for grouping. It works well for me... It also adds some additional theming capability, so you can theme the label of the summary rows.
It takes a slightly different route than the patches above.
I am not yet using Views 3, and when I tried migrating to Views Calc 3.x-dev and Views 3.x-dev, the views I am using completely didn't work even as a starting point, so I'm not all that interested in working on the 3.x port at this time...
Anyway, maybe this patch will be useful to someone, and maybe you'd consider adding it to the 1.x branch so at least grouping would be supported.
#69
Somehow the change for theme.inc didn't make it into the last patch. So this additional patch needs to be applied... still getting the hang of the git workflow... sorry!
#70
I tried a different approach to this for Views 3.x, performing calculations on the field output instead of using SQL, that might be useful for some folks. Let me know if you have any thoughts or feedback: http://drupal.org/node/1083142
#71
Attached you find a patch working with Views 3 and 6.x-3.x-dev ViewsCalc.
Diesplaying of Subtotal for each group is possible.Based on #69
#72
@jhodgdon: can't get your patch to apply to:
patching file views_calc_table.inc
Hunk #1 FAILED at 10.
Hunk #2 succeeded at 18 (offset -2 lines).
Hunk #3 succeeded at 33 (offset -2 lines).
Hunk #4 succeeded at 44 (offset -2 lines).
Hunk #5 succeeded at 59 (offset -2 lines).
Hunk #6 FAILED at 83.
Hunk #7 FAILED at 96.
Hunk #8 FAILED at 114.
Hunk #9 FAILED at 165.
Hunk #10 succeeded at 187 with fuzz 2 (offset -10 lines).
Hunk #11 FAILED at 197.
Hunk #12 succeeded at 229 (offset -24 lines).
Hunk #13 FAILED at 243.
7 out of 13 hunks FAILED -- saving rejects to file views_calc_table.inc.rej
applied to views_calc 6.x-1.3.
Any ideas - I'd like to try this? Thanks
#73
Sorry, I gave up on Views Calc with grouping. It doesn't work well with calculated fields from other modules, and that's what I needed. So I ended up creating a simpler table with column sums views style plugin that works better for my purposes. If anyone's interested, post here... it should be up on d.o in a sandbox as soon as I can figure out Git, and I can post a link (although the sandbox project will have several other components to it).
The patches above were against the 6.x-1.x dev branch, not against 6.x-1.3 specifically, so that could be why they don't apply, or maybe the dev branch has been updated since I made the patch.
#74
@jhodgdon: thanks for the update and yes, of course, we'd be interested to see your dev solution up on d.o. Thanks
#75
This is the sandbox project I set up, which has my "add up a column in a table" views style plugin as part of it:
http://drupal.org/sandbox/jhodgdon/1100780
#76
@jhodgdon Thanks for sharing this.
#77
#68 and #69 worked for me.
Drupal 6.22
Views 6.x-2.12
Views Calc 6.x-1.x-dev (2011-fev-25)
#78
#68 and #69 worked for me when details are showing but not when I hide the details and want just the group results.
#79
Re. #62, I had to make a couple of small alterations to the code in views_calc_table.inc after applying the patch to 6.x-1.3 to get it working. Not sure if these things had already been done in the -dev version at the time this was being discussed, but... Both of the changes were to the function query_sub_total as follows:
$this->view->query->orderby = array();after the code that also cleared the fields array. This made my unknown column issue go away.node.nid, but the line above it hadn't been commented out, thus having both limiting clauses in the WHERE clause. So, I commented out that line.The result is the function below:
<?php
function query_sub_total() {
// Create summary rows.
$calc_fields = $this->get_calc_fields();
$calc = $this->view->views_calc_calculation;
$fields = $calc_fields[$calc];
// Empty out any fields that have been added to the query,
// we don't need them for the summary totals.
$this->view->query->fields = array();
$this->view->query->orderby = array(); // <-- clear out the ORDER BY clause that was causing "unknown column" errors
foreach ($this->view->field as $field) {
$query_field = substr($field->field, 0, 3) == 'cid' ? $field->definition['calc'] : $field->table .'.'. $field->field;
$query_alias = $field->field_alias;
if (in_array($field->field, $fields)) {
// Calculated fields.
$this->view->query->add_field(NULL, "$calc($query_field)", $query_alias);
$this->view->query->add_table($field->table, NULL, NULL, $field->table);
}
else {
// Empty fields that have no calculations.
$this->view->query->add_field(NULL, "MAX('')", $query_alias);
}
// Add a dummy field for the groupby.
$this->view->query->add_field(NULL, "MAX('". $calc ."')", "TOTAL_". $calc);
}
// TODO This won't work right with relationships, need a fix here.
if (!empty($this->view->views_calc_nids)) {
// $this->view->query->add_where(NULL, "node.nid IN (%s)", implode(',', $this->view->views_calc_nids)); // <-- this was 'corrected' by the following line in the patch, and so should be removed, no?
$this->view->query->add_where(NULL, "%s.%s IN (%s)", $this->view->base_table, $this->view->base_field, implode(',', $this->view->views_calc_nids));
}
}
?>
I'm now getting group sub-totals with correct sub-totals. Unfortunately, the overall totals are not showing up... anyone else experiencing that problem?
Shawn
#80
subscribe
#81
I attempted to use the patch in #71. It applies to the 3.x dev version OK, and I get the subtotal rows in the view, but nothing is calculated. I don't get any errors either. Had anyone else tried the 3.x dev patch and were you able to get it working?
Thanks,
-=Delty
#82
subscribing