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.

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

Okay here is what I tried and I am not sure why its not working. I am guessing I am doing something wrong with refining the query to the bean.

To ensure I understand the $id =  $this->bean->id;  holds the value of the current loaded bean-id am I correct?

This function throws no error however it does not produce a result either. Can you see what I am doing wrong?

function display() {

     global $db;

    $id =  $this->bean->id;

         $query = "SELECT sum(sales_price_c) as total FROM op_ps_product_sales_cstm WHERE id_c = $id";

         $result = $db->query($query);

         $row = $db->fetchByAssoc($result);

         echo $total_no_of_opportunities = $row['total'];

    echo "

       <script>

       var total = '$total_no_of_opportunities';

       $(document).ready(function(){$('#total_sales_c').after(total); });

       </script>";

  parent::display();

  }

Hello Dan,

     Enclose $id in single quotes. Find below updated query:

     $query = "SELECT sum(sales_price_c) as total FROM op_ps_product_sales_cstm WHERE id_c = '$id'";

Query changed: The query returns nothing and has no error.

Hello Dan,

     Try to echo out the $query. And run this echoed out query in database and check whether it is working fine or throwing some error.

Is it possible to do something like this.

echo "

       <script>

'sales_price_c'.each(function () {

var sale = Number($('sales_price_c').text());

sum = sum + sale;

total = sum;)}

$(document).ready(function(){$('#total_sales_c').after(total); });

       </script>";

Okay I did echo it out and it looked valid had value in $id then I ran the resulting query into the db and received no errors but again no results.

Hello Dan,

     Have you try executing your query directly in database? About your suggestion, I don't know whether it will work or not. You better know as you are handling the CRM. So, try and post it here if you found any solution. Try to echo out query and run it in database. It must work.

Okay I did echo it out and it looked valid had value in $id then I ran the resulting query into the db and received no errors but again no results.

Yeah, then there is an issue with the query. try to run query without sum() function.

The query does run however no results are displayed. I used the diagnostic tool and obtained a complete list of tables and related tables. I think I need to load the bean with all related tables. In order to get this to work.

I believe I saw something like this $bean->load_relationship(module name) or something. Then I should be able to run the query. What are your thoughts?

-edit-

I attempted the $bean->load_relationship(module name) and this did not work. The query just spit out a "1" Clearly this is not the sum of any of the product prices that it would encounter.

I am still in need of assistance. Perhaps with loading the bean with all related data so that I could access the correct fields then apply the script.

Does anyone know how to do this. I was looking and found HOWTO: Using the bean instead of SQL all the time. « Sugar Developer Blog – SugarCRM  however I was unsuccessful with that approach.

The query does run however no results are displayed. I used the diagnostic tool and obtained a complete list of tables and related tables. I think I need to load the bean with all related tables. In order to get this to work.

I believe I saw something like this $bean->load_relationship(module name) or something. Then I should be able to run the query. What are your thoughts?

-edit-

I attempted the $bean->load_relationship(module name) and this did not work. The query just spit out a "1" Clearly this is not the sum of any of the product prices that it would encounter.

I am still in need of assistance. Perhaps with loading the bean with all related data so that I could access the correct fields then apply the script.

Does anyone know how to do this. I was looking and found HOWTO: Using the bean instead of SQL all the time. « Sugar Developer Blog – SugarCRM  however I was unsuccessful with that approach.

Okay I figured it out!!!!

<?php

if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');

require_once('include/MVC/View/views/view.detail.php');

class OpportunitiesViewDetail extends ViewDetail {

  function OpportunitiesViewDetail(){

  parent::ViewDetail();

  }

  function display() {

$account = new Opportunity();                               //$var = new ModuleName() in singular form

$account->retrieve($_REQUEST['record']);           //This grabs the record

$contacts = $account->get_linked_beans('opportunities_op_ps_product_sales_1','Contact');         

                                                                             // ^this uses the get_linked_beans(linked var name(found in the vardefs) , name of the                                                                                 //  ^object you are creating. The name can be anything you like.

                                                                           // then just loop through the created associations to get fields.

foreach ( $contacts as $contact ) {

    $total += $contact->sales_price_c;         //add the value of each sale to the variable

}

          //then populate the field with the value in the $total var

echo "

       <script>

    var total = '$total';

       $(document).ready(function(){

    $('#total_sales_c').after(total); });

       </script>";

  parent::display();

  }

}

?>

Thank you for all your help this was a great learning experience. Hopefully this will help others.

Hello Dan A,

     I am very glad that you found the solution yourself. Keep going.