Calculating totals, minimum, maximum and average values in WordPress Tables

Table of Contents

Video tutorial

Sum/Totals row in wpDataTables

From version 5.7, the calculation functions can also be used in Formula Columns.

For many cases, it is useful to calculate a sum, average, minimum and maximum of all the values for a given column. For example, total sales within a month, the total price of products in an order, calculating maximum profit, minimum wage, average grade etc. wpDataTables supports a sum / totals, average, minimum and maximum row for numeric columns, see this table as an example, and try to filter it by a date range to see how the totals recalculate:

Date	Heineken	Amstel	Tuborg	Carlsberg
29/08/2019	892	302	213	969
30/08/2019	628	322	503	255
31/08/2019	105	272	301	259
19/09/2001	956	441	299	268
19/09/2002	239	967	629	453
19/09/2003	483	609	689	451
19/09/2004	956	793	966	843
19/09/2005	507	522	210	777
19/09/2006	331	576	359	983
19/09/2007	722	172	758	493
19/09/2008	390	699	726	157
19/09/2009	439	941	259	983
19/09/2010	588	138	184	900
19/09/2011	152	783	155	716
19/09/2012	280	642	447	473
13/09/2019	684	691	315	290
14/09/2019	680	433	750	593
15/09/2019	549	420	884	351
16/09/2019	140	112	590	940
17/09/2019	306	233	295	399
18/09/2019	664	583	528	151
19/09/2019	923	235	937	637
20/09/2019	175	658	640	150
21/09/2019	476	582	106	433
22/09/2019	665	338	132	241
23/09/2019	117	196	390	667
24/09/2019	330	558	978	417
25/09/2019	638	393	576	957
26/09/2019	262	628	844	598
27/09/2019	712	966	932	579
28/09/2019	279	787	445	561
29/09/2019	321	489	842	750
30/09/2019	833	899	210	940
19/10/2001	534	279	987	524
19/10/2002	822	326	753	800
19/10/2003	290	435	476	984
19/10/2004	737	765	893	407
19/10/2005	723	682	245	182
19/10/2006	270	271	703	242
19/10/2007	572	902	755	851
19/10/2008	754	704	1,000	208
19/10/2009	166	590	745	366
19/10/2010	119	666	387	466
19/10/2011	208	414	594	557
19/10/2012	964	287	601	405
13/10/2019	907	138	685	702
14/10/2019	721	231	426	321
15/10/2019	353	245	379	719
16/10/2019	375	571	414	165
17/10/2019	890	721	287	310
18/10/2019	647	801	601	244
19/10/2019	393	786	262	889
20/10/2019	382	636	296	537
21/10/2019	400	640	651	259
22/10/2019	783	864	135	168
23/10/2019	178	604	310	852
24/10/2019	940	199	580	792
25/10/2019	597	772	306	500
26/10/2019	135	683	881	263
27/10/2019	279	951	947	235
28/10/2019	532	710	741	108
29/10/2019	451	924	870	569
30/10/2019	816	696	120	806
31/10/2019	872	247	627	657
19/11/2001	713	956	898	343
19/11/2002	522	367	215	138
19/11/2003	154	952	889	299
19/11/2004	417	656	563	340
19/11/2005	711	525	495	691
19/11/2006	985	884	588	792
19/11/2007	484	602	676	676
19/11/2008	812	612	820	428
19/11/2009	478	535	705	686
19/11/2010	647	946	225	471
19/11/2011	511	338	934	402
19/11/2012	427	522	853	551
13/11/2019	788	247	834	113
14/11/2019	217	108	883	800
15/11/2019	477	948	644	596
16/11/2019	395	257	679	561
17/11/2019	749	123	551	829
18/11/2019	564	242	757	333
19/11/2019	918	351	649	163
20/11/2019	287	875	129	741
21/11/2019	663	400	976	104
22/11/2019	660	448	473	816
23/11/2019	413	694	814	141
24/11/2019	960	531	964	384
25/11/2019	781	440	511	669
26/11/2019	287	225	695	416
27/11/2019	302	678	553	638
28/11/2019	777	843	749	963
29/11/2019	684	172	982	270
30/11/2019	621	839	156	645
19/12/2001	748	416	137	466
19/12/2002	528	721	623	320
19/12/2003	690	633	196	972
19/12/2004	997	923	911	619
19/12/2005	485	119	430	865
19/12/2006	303	900	868	248
Date	Heineken	Amstel	Tuborg	Carlsberg
	∑ = 54,387	∑ = 55,148	∑ = 57,774	∑ = 52,211
	Avg = 543.87	Avg = 551.48	Avg = 577.74	Avg = 522.11
	Min = 105	Min = 108	Min = 106	Min = 104
	Max = 997	Max = 967	Max = 1,000	Max = 984

Adding all these features is simple. In the column settings, click on the Data tab and check the Calculate checkboxes for the selected column you want to calculate these functions.

If these checkboxes are checked for at least one row, a functions row will appear in the bottom.
Functions are recalculated on table redraw (i.e. when you filter).

Also take note of the shortcode below each calculated function.

By pasting the shortcode, you can display the results of the functions somewhere outside the table, or you can paste this code in a cell belonging to another table.
To achieve this you will need to check the Parse shortcodes option on the wpDataTables Settings page.

Go to WordPress admin, and open wpDataTables
Click on Settings
Check the “Parse shortcodes” checkbox

Each calculation function shortcode have additional parameters that can be useful:

label – You can overwrite label throw this shortcode parameter for example for sum(can be used for all shortcode calculation functions)

[wpdatatable_sum table_id=2846 col_id=23013 label="My SUM label"]
By default this parameter is null and will be used value from Global label settings.

value_only – If you need to show only value, without additional html around it you can pass this parameter with value 1. By default is set on 0. For example for average function to show only value (can be used for all calculation shortcode functions)

[wpdatatable_avg table_id=2846 col_id=23013 value_only=1]

Please note that calculation functions cannot be used in wpDataCharts and in the Simple table.

Calculating totals, minimum, maximum and average values in WordPress Tables

Video tutorial

Sum/Totals row in wpDataTables

Read more