Sum of Rows in Queries and Templates

Hi @jammerpro,

Thanks for creating a new thread! If you’re using Data Tables to display this table, you can utilize DataTables footer callback to display a sum at the bottom of your chosen column. Please see: DataTables example – Footer callback

The caveat is that you’d have to create your own JavaScript function for this to work. The above link has a sample of the working function, and to add the function in your table’s Advanced Settings, we have a sample here: Advanced Settings – Using JavaScript Functions

Hope that helps! Let us know if you have more questions. ??

Hi Kim L (@kimmyx),

Thanks for your response. The footer callback is a possibility, but first I need to know how to display the following in a virtual row so I can get the timeslip balance to the data table:

(time_spent*wage+charge-payment)

Look at my query above, which works in queries to give the result after each row… It’s that row I want to use in the callback.

How do I do that in data tables? Is there such a thing as a virtual row?

Thanks again!

• This reply was modified 1 year ago by jammerpro.

Hi?Kim L?(@kimmyx),

Just tried the script you suggested to total a known row with a callback, but kept getting a parse error.

My Database is: CloudLawApp
Table: billing
Columns are: date_b,client,id,employee,job_type,job_type2,time_spent,wage,charge,payment,description

Not sure how to fix the syntax in the script you suggested so that I can at least total a known column in the footer. If you could fill in the example using my column “payment” in database “CloudLawApp” in table “billing” it would help me to learn how to convert those javascripts into your Advanced Section.

Also, I am still not sure how to add a virtual column to my table to show the result I need at the end of the row (time_spent*wage-charge+payment).

Any guidance would be greatly appreciated.

Hi @jammerpro,

You have two options to add a calculation to your query. Either create a database view and use your view in Data Tables or use a custom query. The custom query feature requires a premium license, creating a database view is possible with the free version as well. Your query should look something like this:

select billing.*
, client.client
, client.id
, (billing.time_spent*billing.wage+charge-payment) as "total"
from billing
, client 
WHERE billing.client=client.id 
AND client.id=44 
ORDER BY client.client ASC

You might need to add the table alias before column charge-payment.

Can you share a public URL so we can view the data table and error you get? Please use the contact form on the plugin website if you prefer to share this information in private.

Thanks,
Peter

Hi @peterschulznl

Thanks so much for your help.

Kindly take a quick look at my very first post in this thread as follows:

_____________________________________________________________________

How do I get a GRAND TOTAL of the “Totals” at the bottom?
Here’s the code:

select billing.*, client.client, client.id, (time_spent*wage+charge-payment)from billing,client WHERE billing.client=client.id && client.id=44 ORDER BY client.client ASC

Works perfectly, except, there is no way I know of using just a MySQL select to get the GRAND TOTAL I seek and place it at the end of the result column.

____________________________________________________________________________

Kim L @kimmyx was kind enough to direct us to https://datatables.net/examples/advanced_init/footer_callback.html to get footer callback code to place in the advanced editor. Also loaded your Code Manager to load some JQUERY.

The Javascript Kim supplied was a good example, but was not just a cut and paste. We were able to use the Code Manager and the Advanced Section and created shortcodes from the Table Builder to create a Page to display the result which worked! HOWEVER… while the shortcodes work perfectly on the Page, the View and Test Buttons under the table in Table Builder DO NOT. They crash and keep processing. Those little blue balls of death keep spinning. If we remove the shortcodes, the View and Test under the table in the Table Builder work perfectly; HOWEVER, then the shortcodes don’t work on the Page. Damned if you do, damned if you don’t. LOL.

THE IMPORPORTANT QUESTION WE NEED ANSWERED IS…

In the JQuery Code we used in the Code Manager, or the JSON we used in the Advanced Section,, how do we disable your Table Builder from accessing the footer callback in the Advanced Section together with the JQUERY code in your Code Manager, while allowing it to generate a shortcode that works in the Page?

That would solve the problem.

Hopefully, you understand what I’m asking.

Best wishes on a great product and fantastic community!

• This reply was modified 1 year ago by jammerpro.
• This reply was modified 1 year ago by jammerpro.

Hi @jammerpro,

Do you see any console error when testing the table in the table builder?

The code in the Code Manager shouldn’t affect the table builder since the shortcode is placed on your frontend page.

You can send us a message via Contact | WP Data Access if you want to share more details privately.

We’ll wait for your message! ??

Kim L @kimmyx) and Passionate Programmer Peter (@peterschulznl),

We have gone beyond the point above, and the shortcode for the Advanced Options in the Table code under the Test in Table Builder, and the shortcode the Extra Code we inserted on the Front End Page with Code Manager are co-existing, but the reports DO NOT WORK in the same manner.

The Page gets a page column total now, but we are having trouble with the pagination and Select Box issue, which you resolved. We may now be able to get something meaningful through search because we will get to all entries from the database.

We will work a bit more on this and report back.

It would be a great feature to have an easy way to sum columns across all entries in the database, and get a Grand Total for all the Totals of all columns and entries.

We are fixated on building a professional, running balance ledger and invoice system geared to services with time entries we enter in the form.

I’ll be back.

• This reply was modified 1 year ago by jammerpro.