Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2

    How to get the total of a column based on recordID


    I have been told to ask my question in this forum as i posted the thread on the PHP forum.

    basically i have a utility page showing bills for a property its shows a list of all bills paid to that property, what i need is a total of those show bills for that property. I have tried lots but nothing seems to work. the way my code stands at the moment is the results are showing just the total for the first property in the table.

    PHP Code:
    $colname_rsProperty "-1";
    if (isset(
    $_GET['recordID'])) {
      
    $colname_rsProperty $_GET['recordID'];
    }
    mysql_select_db($database_hostprop$hostprop);
    $query_rsProperty sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID"GetSQLValueString($colname_rsProperty"text"));
    $rsProperty mysql_query($query_rsProperty$hostprop) or die(mysql_error());
    $row_rsProperty mysql_fetch_assoc($rsProperty);
    $totalRows_rsProperty mysql_num_rows($rsProperty);
    //
    mysql_select_db($database_hostprop$hostprop);//this query is showing all totals from the column host_editpropUtil.utilityAmount
    $query_rsSum sprintf("SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id GROUP BY host_editprop.prop_id"GetSQLValueString($colname_rsProperty"text"));
    $rsSum mysql_query($query_rsSum$hostprop) or die(mysql_error());
    $row_rsSum mysql_fetch_assoc($rsSum);
    $totalRows_rsSum mysql_num_rows($rsSum); 
    the results are being echoed as

    PHP Code:
              <?php
             
    echo $row_rsSum['utilityTotal'];
              
    ?>
    thanks in advance
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,138
    Rep Power
    4274
    your SUM query is fine

    if you're only seeing the sum for the first property, then your php routine is flawed

    moving thread to php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,383
    Rep Power
    594
    The question entered here is different that what was asked on the PHP forum. The problem in that thread implied a problem including the correct records in the sum.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by gw1500se
    The question entered here is different that what was asked on the PHP forum. The problem in that thread implied a problem including the correct records in the sum.
    Sorry im confused now..this is the same problem in both threads. where shall i put my question then? shall i repost this on the php forum?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,383
    Rep Power
    594
    No need to make it clear what the problem is. If the sum returned from the query is not including the proper records to get the right total, then state that. You probably should not have included the PHP code in the MySQL forum. Just post the select statement you are using then exlain what is missing from the sum. You will probably need to provide more information about your database structure.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,138
    Rep Power
    4274
    Originally Posted by gw1500se
    Just post the select statement you are using then exlain what is missing from the sum.
    according to post #1 in this thread, his php was printing only the first property's total

    the SUM query looks fine, but this part is suspect --
    PHP Code:
    <?php 
             
    echo $row_rsSum['utilityTotal']; 
              
    ?>
    disclaimer: i don't do php, but this is only one echo, and that's consistent with the supplied symptom of only the first property's total being displayed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,138
    Rep Power
    4274
    Originally Posted by jonnyfreak
    shall i repost this on the php forum?
    no

    you might have missed it, but i moved this thread to the php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,383
    Rep Power
    594
    That echo is the value returned by the query. It is whatever single value the select statement generated. The OP needs to clarify what that value should be compared to what it is. Unless I am not understanding what the OP wants, it is the sum of that column of all the records meeting the selection criteria. I am under the impression that the OP does not know how to set up the select statement to include all the desired records for the proper total.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,138
    Rep Power
    4274
    Originally Posted by gw1500se
    That echo is the value returned by the query. It is whatever single value the select statement generated.
    you might be missing the important fact that the query returns more than one row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,383
    Rep Power
    594
    Is not 'sum' going to have the same value in every row, since it is the total of a column of the selected rows?
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,138
    Rep Power
    4274
    Originally Posted by gw1500se
    Is not 'sum' going to have the same value in every row, since it is the total of a column of the selected rows?
    no

    it's a GROUP BY query, my friend
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,383
    Rep Power
    594
    That is why I thought this was a MySQL question. The total for every row will be the same until the group changes? Then the OP's code needs to determine when the group changes and use the total for that group. Unless I am still not understanding what sum does when used with group by. In any case I'm not sure what the OP really wants so we need to wait for a response to clarify.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,138
    Rep Power
    4274
    Originally Posted by gw1500se
    Unless I am still not understanding what sum does when used with group by.
    this is likely

    the result set would look something like this --

    X 225
    Y 407
    Z 621

    multiple rows, one for each property, showing the sum for each property

    his php code is showing only 225, and none of the others

    let me say it again -- his query is fine

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    this is a PHP issue. He calls the fetch function only once, so obviously he only gets the first row. He simply needs a loop instead:

    PHP Code:
    while ( $row_rsSum mysql_fetch_assoc($rsSum) )
        echo 
    $row_rsSum['utilityTotal'].'<br />'
    That's it.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    70
    Rep Power
    2
    Originally Posted by Jacques1
    Hi,

    this is a PHP issue. He calls the fetch function only once, so obviously he only gets the first row. He simply needs a loop instead:

    PHP Code:
    while ( $row_rsSum mysql_fetch_assoc($rsSum) )
        echo 
    $row_rsSum['utilityTotal'].'<br />'
    That's it.
    thank so much for you help, i tried your suggestion but i am now getting just the 1st value in the column utilityAmount in the table host_editpropUtil

    i dont know if i have messed something up in my sql or does that look correct

    PHP Code:
     $colname_rsProperty "-1"
    if (isset(
    $_GET['recordID'])) { 
      
    $colname_rsProperty $_GET['recordID']; 

    mysql_select_db($database_hostprop$hostprop); 
    $query_rsProperty sprintf("SELECT * FROM host_editprop, host_editpropUtil, host_editpropUtilComp WHERE host_editprop.prop_id = %s AND host_editpropUtil.prop_id = host_editprop.prop_id AND host_editpropUtilComp.utilID = host_editpropUtil.UtilPropNameID"GetSQLValueString($colname_rsProperty"text")); 
    $rsProperty mysql_query($query_rsProperty$hostprop) or die(mysql_error()); 
    $row_rsProperty mysql_fetch_assoc($rsProperty); 
    $totalRows_rsProperty mysql_num_rows($rsProperty); 
    // 
    mysql_select_db($database_hostprop$hostprop);//this query is showing all totals from the column host_editpropUtil.utilityAmount 
    $query_rsSum sprintf("SELECT SUM(host_editpropUtil.utilityAmount) AS utilityTotal, host_editprop.prop_id, host_editpropUtil.prop_id FROM host_editpropUtil, host_editprop WHERE host_editpropUtil.prop_id = host_editprop.prop_id GROUP BY host_editprop.prop_id"GetSQLValueString($colname_rsProperty"text")); 
    $rsSum mysql_query($query_rsSum$hostprop) or die(mysql_error()); 
    $row_rsSum mysql_fetch_assoc($rsSum); 
    $totalRows_rsSum mysql_num_rows($rsSum); 
    the results are echoed out as you suggested
    PHP Code:
              <?php
             
    // echo $row_rsSum['SUM(host_editpropUtil.utilityAmount)'];
    while ( $row_rsSum mysql_fetch_assoc($rsSum) ) 
        echo 
    $row_rsSum['utilityTotal'].'<br />'
              
    ?>
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo