How to query db and sum the rows returned

Question

Hello,

I have a need to query the DB for all products sold to a client then get the total. This then needs to be displayed in a field.

I am using SugarCRM CE 6.5.13

I would greatly appreciate any assistance.

Thanks.

Dan A · Accepted Answer

Hello Dan A, First you need to add one field in Opportunity module, then add this field to detailview layout only. Later, in view.detail.php file you can query in function display and using jquery function '.after', you can append the result to that newly created field. Kindly find below dummy code: function display() { global $db; $id = $this->bean->id; $query = "SELECT COUNT(id) AS total FROM opportunities WHERE status = 'SOLD' and deleted = 0"; $result = $db->query($query); $row = $db->fetchByAssoc($result); $total_no_of_product_sold = $row['total']; echo " "; parent::display(); } You need to add this code in your custom/modules/Opportunities/views/view.detail.php file. Here total_no_of_product_sold is newly added field in Opportunity module. Please don't mind, but it seems that you are novice developer. I would suggest you to google more about SugarCRM and read various posts and try to implement in your CRM. Let me know if you need any more clarification.