Simple solution for SUM total in element

[dewebkrant]

Just seeking for a simple solution to show the sum total of column in a form element. Sum total in list is what I have but I want this value to be shown in a form field. Tried calc element with many options but nothing worked sofar. Hoped to have an option to enter a simple SQL query like:
SELECT SUM('Bedrag') FROM 'boekingen_inkomsten';
but that is not how the calc element works? Question is (are):
- which type of element I should use to show the sum total of other column
- how to use an element to show a SQL query (would give much more options.... - using plotalot for example and here I can just use a SQL statement to show a certain value - is this possible in fabrik?)
- or how to show the value from the Plotalot SQL statement in element (field?)?
Hope to continue in this adventure - tried to find my "simple solution" on the forum but no luck sofar.
Thanks for this great extension and kind regards,
Meindert

 

[NickC4555]

You can query the joomla database in a calc element using JDatabase.

https://docs.joomla.org/Selecting_data_using_JDatabase

 

[dewebkrant]

Thanks Nick for your quick reply. Trying to implement the proposed solution and have:
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select SUM($db->quoteName(array('Bedrag')));
$query->from($db->quoteName('boekingen_inkomsten'));
$db->setQuery($query);
$results = $db->loadObjectList();

But: not working. Hoped to have an inbuilt Fabrik option to simply load the SUM in an element.
Thanks

 

[dewebkrant]

Managed with the Plotalot solution (http://fabrikar.com/forums/index.php?threads/fabrik-and-plotalot-with-variables.44027/) - using sql string in Plotalot single item and placing the plotalot shortcode in calc element and disabling the Ajax option in the calc element. Ok, this opens a lot more options. Thanks.

 

[NickC4555]

Try this:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('sum('.$db->quoteName('Bedrag').')');
$query->from($db->quoteName('boekingen_inkomsten'));
$db->setQuery($query);
$results = $db->loadResult();

return $results;

 

[dewebkrant]

Yes Nick, that is working also! So now we have two solutions. Great, thanks a lot.