I have two content types - contact and payment. Payment have field_contact that is an reference to contact and also field_payment_amount which is decimal.

In views I'm showing list of contacts (with filter by type = contact) but also would like to show SUM of payments for given contact. I added relationship: Entity Reference: Referencing entity to entities that are referencing my contact by field field_contact, turned on aggregation, and added to view field_payment_amount aggregated as SUM, and using my relationship. In this configuration all contacts are shown but field that should show my sum is empty.

Next I added another filter (by type), now using my relationship and for this relationship filtering by type = payment. In this configuration I can see my sums - great! But! If contact have no payments - it is excluded from table completely.

How can I show contacts without payments with "0" as sum of payments and calculate proper sums for contacts with payments? I feel like I'm missing something simple, or maybe not?


Ok, I reduced problem to most simple case, and analyzed query. Now interesting thing is that looks like the same query executed manually by me, and by view - gives different results O_o .


SELECT node.nid AS nid, MIN(field_contact_node.nid) AS field_contact_node_nid, 'node' AS field_data_field_payment_amount_node_entity_type, SUM(field_contact_node__field_data_field_payment_amount.field_payment_amount_value) AS field_contact_node__field_data_field_payment_amount_field_pa
{node} node
LEFT JOIN {field_data_field_contact} field_data_field_contact ON node.nid = field_data_field_contact.field_contact_target_id AND (field_data_field_contact.entity_type = 'node' AND field_data_field_contact.deleted = '0')
LEFT JOIN {node} field_contact_node ON field_data_field_contact.entity_id = field_contact_node.nid
LEFT JOIN {field_data_field_payment_amount} field_contact_node__field_data_field_payment_amount ON field_contact_node.nid = field_contact_node__field_data_field_payment_amount.entity_id AND (field_contact_node__field_data_field_payment_amount.entity_type = 'node' AND field_contact_node__field_data_field_payment_amount.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('contact')) ))
GROUP BY nid, field_data_field_payment_amount_node_entity_type

Views output:


Query manual execution result:

enter image description here

Does anyone have possible explanation of this interesting behavior?

2 Answers 2


I just got this working by doing basically what you did:

Relationship: Content referencing Content from field_contact, do not tick 'Require this relationship'

Aggregation: Yes


  1. Title: no relationship

  2. payment amount: set relationship to Content referencing Content from field_contact. Set no results behavior to '0'. Set aggregation to 'SUM', Group column to 'Value'

Works fine.

Exported View

$view = new view();
$view->name = 'test';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'test';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'test';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'perm';
$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'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: Entity Reference: Referencing entity */
$handler->display->display_options['relationships']['reverse_field_contact_node']['id'] = 'reverse_field_contact_node';
$handler->display->display_options['relationships']['reverse_field_contact_node']['table'] = 'node';
$handler->display->display_options['relationships']['reverse_field_contact_node']['field'] = 'reverse_field_contact_node';
/* Field: Content: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
/* Field: SUM(Content: payment_amount) */
$handler->display->display_options['fields']['field_payment_amount']['id'] = 'field_payment_amount';
$handler->display->display_options['fields']['field_payment_amount']['table'] = 'field_data_field_payment_amount';
$handler->display->display_options['fields']['field_payment_amount']['field'] = 'field_payment_amount';
$handler->display->display_options['fields']['field_payment_amount']['relationship'] = 'reverse_field_contact_node';
$handler->display->display_options['fields']['field_payment_amount']['group_type'] = 'sum';
$handler->display->display_options['fields']['field_payment_amount']['empty'] = '0';
$handler->display->display_options['fields']['field_payment_amount']['settings'] = array(
  'thousand_separator' => ' ',
  'decimal_separator' => '.',
  'scale' => '2',
  'prefix_suffix' => 1,
/* Sort criterion: Content: Post date */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: 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(
  'contact' => 'contact',

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['path'] = 'test';
  • Can you please export your view and paste it here? Commented Feb 14, 2015 at 19:57
  • Thanks, but I meant view and not query :) . Commented Feb 14, 2015 at 20:31
  • I suggest you do a new test install of Drupal and recreate it there for yourself. Commented Feb 14, 2015 at 20:39
  • Just an afterthought, did you see step 2 re. Set no results behavior to '0' Commented Feb 14, 2015 at 20:56
  • Yes, unfortunately I was at the point of your view before, and the problem is, that it is not aggregating payments properly. I added payment, and still have all "0" everywhere. I'm going to try this out on other installment of Drupal. Commented Feb 14, 2015 at 21:08

I was debugging like crazy to figure this out and then after checking some values in views_handler_field_field I realized what was wrong, and for sure it weren't Views! I forgot, or rather though of it as irrelevant, that there was third content type - a comment, that had a field "field_contact". The problem occurred because Views when summing up, was also using existing "comment" nodes, but because they didn't have "field_payment_amount" at all - Views got confused. So the real answer is that we need to filter out any additional types that are using "field_contact" field in our relationship.

I guess Views could act a bit better on this, and not get confused but use only available fields from proper nodes to create a sum, but it isn't much problem as long as we can filter out useless (for SUM) types that are using our reference field.

This explains why I got random/wrong results without type filtering on relationship, but when I added filter for "payment" - I didn't get "0" results, because then view assumed that relationship is required. The proper solution is then EXCLUDING wrong types that are in our relationship.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.