How can I sum up fields that reside in multiple nodes?

Question

I am using Drupal as an inventory management system and wanted to know how can I sum up fields to get a net total?

Say I purchased 10,000 widgets over 2012, and I want to be able to sum up all the fields in those nodes to get a final total then have it displayed in a block giving me a summary of how much I spent during 2012. I want to be able to archive this data into a type of report.

I know I should use the Views module, but I want to know how others would accomplish this situation.

What kind of performance roadblocks could I run into, if I had 1 million nodes? Would my site crash? can this be lazy loaded. if needed?

Answer 1

The SUM functionality is actually built into Views for D7.

If you go to your Advanced settings and enable Aggregation, you will see the Aggregation settings applied to each field.

Click on 'Aggregation settings' for the field you'd like to do the SUM functions for and you can select 'SUM' from the 'Aggregation type' dropdown as shown in this screenshot

Taken from Source Sum a field

Answer 2

The views calc module allows you to have total/sum values at the end of a list of results.

If the layout it provides isn't what you are after you could then tweak it using template overrides & css.

If you just want the total I would do a custom block that uses a bit of custom sql. If it is a reasonably basic sql statement it will run pretty fast.

I would also implement a caching method so that the sql is not run every time, but maybe once a day or week or whatever is sensible for the use case.

You can use drupal's cache system for this, which is pretty straight forward, with a cache_set() and cache_get().

If it wasn't something that could be cached you could use the method from Oleg's answer and write the sums to a database table whenever content is added, edited, removed, etc. and then just pull in that value. You could do this using node api hooks.

If the result is different per user then you have to store the values per user in the cache or store a value per user in the database.

Answer 3

Another module that can be used for addressing these requirements is the Forena module. Here is a quote from one of its community docu pages, i.e. Reasons for considering Forena:

"Why not use views instead of forena?" ... The answer is generally something along the lines of "Most forena users love views, views is great! If you want to report against external data though, or you want to do aggregation or analytics, Forena is much more suited to address these requirements."

For an illustration (demo) of the kind of reporting (and charting ...) features it offers, which seem pretty close to what you might be looking for, refer to its Sample reports and graphs, such as:

• Master Summary with drill down (consider 'State' as corresponding to the year, like 2012, in the question here).
• Users in cities assigned to a state (consider 'Nr of users' as corresponding to the 'field' in the question here).

Disclosure: I'm a co-maintainer of this module,
I hope this does not violate the site's policy on self-promotion.

Answer 4

I see two possibilities. Correct me If I am wrong.

1. In views, you add a footer PHP field, that calculates the sum. You can dpm() the variable $view, $row , etc.

2. Create custom database table and a script (in a custom module) that calculates the thing and writes it to the database.After that make it a cron task, to overwrite the value and then again in the footer, make a call to this database field.