How to display the sum of 2 lists

[verzevoul]

Hi!
I need to display the sum of two lists in an element on the third list.

example.
List1
a.customers
b.adress
c.total expenses (this is my calc element)

List2
d.customer (db join on first list)
e.employe expenses (field to be calculated but the sum of it)

List3
f.(db join on first list)
g.building expenses (field to be calculated but the sum of it)

So I made it to show the sum for e and g which are calc elements and at the bottom I display the sum of these elements.
Now I want to display these 2 sums in an element on c.
Is this possible some how?

 

[verzevoul]

I searched in forums and found something similar
http://fabrikar.com/forums/showthread.php?t=23586&highlight=calc+sums
but I need it with sum and not division.
In this thread I didnt see the answer.

 

[cheesegrits]

I presume this needs to be done on a per-customer basis, so the sum of values from the second two tables needs to be broken down by the customer ID.

This could be done with a calc element, but it would be quite an expensive overhead on database processing, as it would incur a database lookup for every row being displayed on the main customers list. The calc PHP would be something like:

$db = JFactory::getDbo();
$db->setQuery("SELECT SUM(list2.employee_expenses) + SUM(list3.building_expenses) AS total_expenses FROM list1 LEFT JOIN list2 ON list1.id = list2.customer_id LEFT JOIN list3 ON list1.id = list3.customer_id WHERE list1.id = {list1___id_raw}");
return $db->loadResult();

You'll have to set "Calc on Save Only" to No.

-- hugh

 

[verzevoul]

Well .. I tried this

$pelatakos = (int)'{aaaoikonomika___onomateponimo1_raw}';

$db = &JFactory::getDBO();
$db-> setQuery("SELECT SUM(`Price`) AS TOTAL FROM `aaaoikonomika`,`aaaoikonomika___eispraxi` WHERE aaadiamerismata___onomateponimo = aaadiamerismata___id AND aaaoikonomika___onomateponimo1=$order_id");
$result = $db->loadResult();
return $result;

but it doent work. I know I m wrong, but Ill try to send you printscreens to see if you can help!

 

[verzevoul]

the printscreens

printscreens

 

[cheesegrits]

You query has several errors in it. In general, if you aren't sure of the query you need, you should try it by hand in something like phpMyAdmin first, substituting actual data for any placeholders you are using.

Where is $order_id supposed to be coming from? You never assign anything to that.

You also appear to be trying to use a field name rather than a table name in your FROM, although you've used a comma instead of a period ... but there should only be the table name there anyway.

So ... try playing with that query in phpMyAdmin, using some value you know exists for $order_id, until you get the result you expect. Then paste that back in to you code, and replace the test value with $order_id (once you have fixed assigning a value to $order_id, see my first point above).

-- hugh