Calculating totals, minimum, maximum and average values in WordPress Tables
Video versionA video overview of responsive tables in wpDataTables
Sum/Totals row in wpDataTablesHow to show totals for numeric columns in tables
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)