How to query db and sum the rows returned

Hi

you can query by client id with product_client relation table then it will give all product record ids.

then make a loop for count of total from product modules.

Thank you for your reply. I am fairly new to SugarCRM CE and forgive me a follow up question. How do you do this. I have tried many times and I am not getting it. Could you show me a mock up of code. Thank you for your help.

Hey Dan A,

     Instead of using logic stated by Mehul, you can use mysql "count" function to count the number of product sold. Let me know if this helps you or not.

Thank you for your reply. I am still new to working with Sugar and I am not sure how to do what you have suggested. Can you provide more specifics please?

Hello Dan A,

  You can achieve this by using following query:

  $query = "SELECT count(product_sold) as total_product_sold FROM product WHERE status = 'SOLD' AND deleted = 0";

  So, by using COUNT() function, you can achieve your aim. Let me know if you get success

Thank you!

Where do i put this to ensure it will work, and is there a way to get the sum of the prices of each of the sold products?

Hey Dan A,

     First let me know, where do you want to show this total count of product sold in your CRM? and second, in mysql there is sum() function which you can use to get the sum of prices of each of the sold products. Let me know if you need more help.

Ideally I would like to show this total in the details view of the Opportunities module.

Yes I would accept any help you can offer.

Thank you very much for you help!.

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 "

  <script>

  var total = '$total_no_of_product_sold';

  $(document).ready(function(){

  $('#total_no_of_product_sold').after(total);

  });

  </script>";

  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.

Thank you for all your assistance. I am indeed a novice to SugarCRM and how it works. I have read numerous articles and posts and have yet to find good solid resources for learning the development side of SugarCRM CE. Again I thank you for all your assistance and you get full credit for the correct answer as you have gone above and beyond in explaining this.

Best regards,

Dan.